USE PreContract3

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.FuncGetExchangeRate', 'FN') IS NOT NULL
	DROP FUNCTION dbo.FuncGetExchangeRate
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-07
-- Description:	Gets exchange rate.
-- =============================================
CREATE FUNCTION dbo.FuncGetExchangeRate()
RETURNS DECIMAL(8, 6)
AS
BEGIN
	RETURN (SELECT TOP 1 a.Rate FROM dbo.ExchangeRate AS a)
END
GO

/****** Object:  UserDefinedFunction [dbo].[GetAssignedBD]    Script Date: 06/13/2011 14:40:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.GetAssignedBD', 'IF') IS NOT NULL
	DROP FUNCTION dbo.GetAssignedBD
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-13
-- Description:	Returns BD assigned to specific user 
-- =============================================
CREATE FUNCTION [dbo].[GetAssignedBD] 
(	
	@employeeID INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT b.Active
	      ,b.BDCode
	      ,b.BDDescription
	      ,b.BDID
	      ,b.BGID
	      ,b.CreateDate
	      ,b.Division
	      ,B.SortNumber
	      ,b.UpdateDate FROM dbo.BD AS b
	INNER JOIN dbo.BDAssignment AS a ON a.BDID=b.BDID
	WHERE a.EmployeeID=@employeeID
)
GO

/****** Object:  UserDefinedFunction [dbo].[GetAssignedBG]    Script Date: 06/13/2011 14:42:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.GetAssignedBG', 'IF') IS NOT NULL
	DROP FUNCTION dbo.GetAssignedBG
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-13
-- Description:	Returns BG assigned to specific user 
-- =============================================
CREATE FUNCTION [dbo].[GetAssignedBG] 
(	
	@employeeID INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT b.Active
	      ,b.BGCode
	      ,b.BGID
	      ,b.CreateDate
	      ,B.SortNumber
	      ,b.UpdateDate FROM dbo.BG AS b
	--INNER JOIN dbo.BGAssignment AS a ON a.BGID=b.BGID
	--WHERE a.EmployeeID=@employeeID
)


GO

/****** Object:  UserDefinedFunction [dbo].[GetAssignedCAT]    Script Date: 06/13/2011 14:44:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.GetAssignedCAT', 'IF') IS NOT NULL
	DROP FUNCTION dbo.GetAssignedCAT
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-13
-- Description:	Returns CAT assigned to specific user 
-- =============================================
CREATE FUNCTION [dbo].[GetAssignedCAT] 
(	
	@employeeID INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT b.Active
	      ,b.CATCode
	      ,b.CATCode_Shortening
	      ,b.CATID
	      ,b.CATName
	      ,b.CreateDate
	      ,B.SortNumber
	      ,b.UpdateDate FROM dbo.CAT AS b
	INNER JOIN dbo.CATAssignment AS a ON a.CATID=b.CATID
	WHERE a.EmployeeID=@employeeID
)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.GetAssignedProvince', 'IF') IS NOT NULL
	DROP FUNCTION dbo.GetAssignedProvince
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-13
-- Description:	Returns province assigned to specific user 
-- =============================================
CREATE FUNCTION dbo.GetAssignedProvince 
(	
	@employeeID INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT b.Active
	      ,b.ClusterID
	      ,b.CreateDate
	      ,b.ProvinceCName
	      ,b.ProvinceCode
	      ,b.ProvinceEName
	      ,b.ProvinceID
	      ,b.SortNumber
	      ,b.UpdateDate FROM dbo.Province AS b
	INNER JOIN dbo.ProvinceAssignment AS a ON a.ProvinceID=b.ProvinceID
	WHERE a.EmployeeID=@employeeID
)
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_1_1]    Script Date: 06/07/2011 17:38:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_1_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_1_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-03
-- Description:	This is for monthly report 1.1.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_1_1] 
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	CREATE TABLE #tmpTable(AdjustDisplayName NVARCHAR(15))
	INSERT INTO #tmpTable (AdjustDisplayName)
	VALUES ('Pre-'),('Test/Lease'),('Try&Buy')
	(SELECT
	  b.ClusterEName AS ClusterEName
	  ,c.ProvinceEName AS ProvinceEName
	  ,a.PreContractID AS PreContractID
	  ,a.OrionPrice AS OrionPrice
	  ,a.CEWPromised AS CEWPromised
	  ,a.CEWME AS SparePrice
	  ,a.OrionME AS OrionME
	  ,a.CFIPrice AS CFIPrice
	  ,CASE a.AdjustID WHEN 21 THEN 'Pre-'
		WHEN 18 THEN 'Test/Lease'
		WHEN 20 THEN 'Test/Lease'
		WHEN 19 THEN 'Try&Buy'
		ELSE 'Others' END AS AdjustDisplayName
	FROM
	  vw_PreContractInfo AS a
	INNER JOIN ProvinceCluster AS b ON b.IsActive=1 AND b.ProvinceClusterID=a.ClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND a.ProvinceID=c.ProvinceID
	WHERE a.ProvinceActive=1 AND a.PreContractAdjust IS NOT NULL)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
			,c.ProvinceEName AS ProvinceEName
			,NULL AS PreContractID
			,NULL AS OrieonPrice
			,NULL AS CEWPromised
			,NULL AS SparePrice
			,NULL AS OrionME
			,NULL AS CFIPrice
			,h.AdjustDisplayName AS AdjustDisplayName
	 FROM dbo.ProvinceCluster AS a
	 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ClusterID=a.ProvinceClusterID
	 CROSS JOIN #tmpTable AS h
	WHERE a.IsActive=1 --AND (NOT EXISTS (SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b WHERE (a.ProvinceClusterID = b.ClusterID) AND (b.PreContractAdjust IS NOT NULL)))
	)
	DROP TABLE #tmpTable
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_1_2]    Script Date: 06/07/2011 18:29:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_1_2', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_1_2
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-07
-- Description:	This is for the monthly report 1.1.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_1_2]
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS decimal(18,6)
	(SELECT b.ClusterEName AS ClusterEName
		   ,c.ProvinceEName AS ProvinceEName
		   ,a.PreContractID AS PreContractID
		   ,a.OrionPrice AS OrionPrice
		   ,a.CEWPromised AS CEWPromised
		   ,a.CEWME AS SparePrice
		   ,a.OrionME AS OrionME
	       ,a.CFIPrice AS CFIPrice
	       ,a.Delivered AS Delivered
		FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.Delivered=1 AND a.ProvinceActive=1)
	UNION
	(SELECT c.ClusterEName AS ClusterEName
		   ,d.ProvinceEName AS ProvinceEName
		   ,NULL AS PreContractID
		   ,NULL AS OrionPrice
		   ,NULL AS CEWPromised
		   ,NULL AS CEWME
		   ,NULL AS OrionME
		   ,NULL AS CFIPrice
		   ,1 AS Delivered
		FROM dbo.ProvinceCluster AS c
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS d ON d.Active=1 AND c.ProvinceClusterID=d.ClusterID
		WHERE c.IsActive=1 AND (NOT EXISTS(SELECT TOP 1 b.PrecontractID FROM dbo.vw_PreContractInfo as b WHERE (b.AdjustID=21 AND b.Delivered=1 AND b.ProvinceID=d.ProvinceID))))
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_1_3]    Script Date: 06/07/2011 18:35:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_1_3', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_1_3
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-07
-- Description:	This is for monthly report 1.1.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_1_3] 
    @CurrentYear INT,
	@CurrentMonth INT,
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
  	DECLARE @startTime AS date
  	DECLARE @beginTime AS date
  	SET @beginTime = '0001-01-01'
	SET @startTime=DATEADD(YEAR, @CurrentYear - 1, @beginTime)
	SET @startTime=DATEADD(MONTH, @CurrentMonth - 1, @startTime)
	DECLARE @endTime AS date
	SET @endTime=DATEADD(year, @CurrentYear - 1, @beginTime)
	SET @endTime=DATEADD(month, @CurrentMonth, @endTime)
	SET @endTime=DATEADD(DAY, -1, @endTime)

	DECLARE @exchangeRate AS decimal(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1000000*@exchangeRate
	
	(SELECT c.ClusterEName AS ClusterEName
	       ,d.ProvinceEName AS ProvinceEName 
           ,SUM(b.[TransferMoney])/@exchangeRate AS TransferMoneySum
           ,@CurrentMonth AS [CurrentMonth]
	  FROM [dbo].[vw_PreContractInfo]  AS a
	  INNER JOIN [dbo].[ProvinceCluster] AS c ON c.IsActive=1 AND a.ClusterID=c.ProvinceClusterID
	  INNER JOIN [dbo].GetAssignedProvince(@employeeID) AS d ON d.Active=1 AND d.ProvinceID=a.ProvinceID
	  INNER JOIN [dbo].[ContractTransfer] AS b ON a.PreContractID=b.PreContractID AND (b.TransferDate BETWEEN @startTime AND @endTime)
	  GROUP BY c.ClusterEName, d.ProvinceEName)
  UNION
	 (SELECT c.ClusterEName AS ClusterEName
		    ,d.ProvinceEName AS ProvinceEName 
		    ,NULL AS TransferMoneySum
		    ,@CurrentMonth AS [CurrentMonth]
	  FROM [dbo].[ProvinceCluster] AS c
	  INNER JOIN [dbo].GetAssignedProvince(@employeeID) AS d ON d.Active=1 AND d.ClusterID=c.ProvinceClusterID
	  WHERE c.IsActive=1 AND (NOT EXISTS(SELECT TOP 1 e.PreContractID FROM dbo.vw_PreContractInfo AS e INNER JOIN dbo.ContractTransfer AS f ON e.PreContractID=f.PreContractID AND (f.TransferDate BETWEEN @startTime AND @endTime) WHERE e.ProvinceID=d.ProvinceID))
	  GROUP BY c.ClusterEName, d.ProvinceEName)
  
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_2_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_2_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-03
-- Description:	This is for monthly report 1.2.
-- =============================================
CREATE PROCEDURE dbo.MonthlyReport1_2_1 
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate AS decimal(18,6)
	--SET @exchangeRate=dbo.FuncGetExchangeRate()
	--SET @exchangeRate=1000000*@exchangeRate
	CREATE TABLE #tmpTable(AdjustDisplayName NVARCHAR(15))
	INSERT INTO #tmpTable (AdjustDisplayName)
	VALUES ('Pre-'),('Test/Lease'),('Try&Buy')
	(SELECT a.CATCode AS CATCode
		   ,a.PreContractID AS PreContractID
		   --,a.OrionPrice AS OrionPrice
		   ,a.CEWPromised AS CEWPromised
		   ,a.CEWME AS SparePrice
		   ,a.OrionME AS OrionME
		   ,a.CFIPrice AS CFIPrice
		   --,a.SparePrice/@exchangeRate AS SparePrice
		   ,CASE a.AdjustID WHEN 21 THEN 'Pre-'
				WHEN 18 THEN 'Test/Lease'
				WHEN 20 THEN 'Test/Lease'
				WHEN 19 THEN 'Try&Buy'
				ELSE 'Others' END AS AdjustDisplayName
		FROM
		  vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS b ON a.CATID=b.CATID
		WHERE a.CATActive=1 AND a.PreContractAdjust IS NOT NULL)
	UNION
	(SELECT a.CATCode AS CATCode
		   ,NULL AS PreContractID
		   --,NULL AS OrieonPrice
		   ,NULL AS CEWPromised
		   ,NULL AS SparePrice
		   ,NULL AS OrionME
		   ,NULL AS CFIPrice
		   --,NULL AS SparePrice
		   ,h.AdjustDisplayName AS AdjustDisplayName
	 FROM dbo.GetAssignedCAT(@employeeID) AS a
	 CROSS JOIN #tmpTable AS h
	 WHERE a.Active=1 --AND (NOT EXISTS(SELECT TOP 1 * FROM dbo.vw_PreContractInfo AS b WHERE (b.PreContractAdjust IS NOT NULL) AND (b.CATID=a.CATID)))
	 )
	 DROP TABLE #tmpTable
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_2_2', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_2_2
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-03
-- Description:	This is for the monthly report 1.2.
-- =============================================
CREATE PROCEDURE dbo.MonthlyReport1_2_2
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate AS decimal(18,6)
	--SET @exchangeRate=dbo.FuncGetExchangeRate()
	--SET @exchangeRate=1000000*@exchangeRate
	(SELECT a.CATCode AS CATCode
		   ,a.PreContractID AS PreContractID
		   --,a.OrionPrice AS OrionPrice
		   ,a.CEWPromised AS CEWPromised
		   ,a.CEWME AS SparePrice
		   ,a.OrionME AS OrionME
		   ,a.CFIPrice AS CFIPrice
		   --,a.SparePrice/@exchangeRate AS SparePrice
		   ,a.Delivered AS Delivered
		FROM
		  dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS b ON b.CATID=a.CATID
		WHERE a.CATActive=1 AND a.AdjustID=21 and a.Delivered=1)
	UNION
	(SELECT a.catcode as CATCode
		   ,NULL AS PreContractID
		   --,NULL AS OrionPrice
		   ,NULL AS CEWPromised
		   ,NULL AS SparePrice
		   ,NULL AS OrionME
		   ,NULL AS CFIPrice
		   --,NULL AS SparePrice
		   ,1 AS Delivered
		FROM dbo.GetAssignedCAT(@employeeID) AS a
		WHERE a.Active=1 AND (NOT EXISTS(SELECT TOP 1 * FROM dbo.vw_PreContractInfo AS b WHERE b.AdjustID=21 AND b.Delivered=1 AND b.CATID=a.CATID)))
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_2_3]    Script Date: 06/03/2011 17:24:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_2_3', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_2_3
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-03
-- Description:	This is for monthly report 1.2.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_2_3] 
    @CurrentYear INT,
	@CurrentMonth INT,
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
  	DECLARE @startTime AS DATE
  	DECLARE @beginTime AS DATE
  	SET @beginTime = '0001-01-01'
	SET @startTime=DATEADD(YEAR, @CurrentYear - 1, @beginTime)
	SET @startTime=DATEADD(MONTH, @CurrentMonth-1, @startTime)
	DECLARE @endTime AS DATE
	SET @endTime=DATEADD(year, @CurrentYear - 1, @beginTime)
	SET @endTime=DATEADD(month, @CurrentMonth, @endTime)
	SET @endTime=DATEADD(DAY, -1, @endTime)

	DECLARE @exchangeRate AS DECIMAL(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1000000*@exchangeRate
	
	(SELECT a.[CATCode] 
      ,SUM(b.[TransferMoney])/@exchangeRate AS TransferMoneySum
      , @CurrentMonth AS [CurrentMonth] FROM [dbo].[vw_PreContractInfo]  AS a
	  INNER JOIN dbo.GetAssignedCAT(@employeeID) AS c ON a.CATID=c.CATID
	  INNER JOIN [dbo].[ContractTransfer] AS b
	  ON a.PreContractID=b.PreContractID AND (b.TransferDate BETWEEN @startTime AND @endTime)
	  WHERE a.CATActive=1
	  GROUP BY a.[CATCode])
  UNION
	(SELECT c.[CATCode] 
		   ,NULL AS TransferMoneySum
		   ,@CurrentMonth AS [CurrentMonth]
	  FROM [dbo].GetAssignedCAT(@employeeID) AS c
	  WHERE c.Active=1 AND (NOT EXISTS(SELECT TOP 1 a.PreContractID FROM dbo.vw_PreContractInfo AS a INNER JOIN dbo.ContractTransfer AS b ON (a.PreContractID=b.PreContractID AND (b.TransferDate BETWEEN @startTime AND @endTime)) WHERE a.CATID=c.CATID))
	  GROUP BY c.[CATCode])
  
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_2_4]    Script Date: 06/07/2011 08:55:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_2_4', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_2_4
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-03
-- Description:	This is for monthly report 1.2.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_2_4] 
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @exchangeRate AS decimal(18,6)
	--SET @exchangeRate=dbo.FuncGetExchangeRate()
	--SET @exchangeRate=1000000*@exchangeRate
	CREATE TABLE #tmpTable (AdjustDisplayName NVARCHAR(15))
	INSERT INTO #tmpTable(AdjustDisplayName)
	VALUES ('Pre-'),('Test/Lease'),('Try&Buy')
	(SELECT a.BGCode AS BGCode
		   ,a.BDCode AS BDCode
		   ,a.PreContractID AS PreContractID
		   --,a.OrionPrice AS OrionPrice
		   ,a.CEWPromised AS CEWPromised
		   ,a.CEWME AS SparePrice
		   ,a.OrionME AS OrionME
		   ,a.CFIPrice AS CFIPrice
		   --,a.SparePrice/@exchangeRate AS SparePrice
		   ,CASE a.AdjustID WHEN 21 THEN 'Pre-'
				WHEN 18 THEN 'Test/Lease'
				WHEN 20 THEN 'Test/Lease'
				WHEN 19 THEN 'Try&Buy'
				ELSE 'Others' END AS AdjustDisplayName
		FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedBG(@employeeID) AS b ON b.BGID=a.BGID AND b.Active=1
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS c ON c.BDID=a.BDID
		WHERE a.BDActive=1 AND a.PreContractAdjust IS NOT NULL)
	UNION
	(SELECT b.BGCode AS BGCode
			,d.BDCode AS BDCode
			,NULL AS PreContractID
			--,NULL AS OrieonPrice
			,NULL AS CEWPromised
			,NULL AS SparePrice
			,NULL AS OrionME
			,NULL AS CFIPrice
			--,NULL AS SparePrice
			,h.AdjustDisplayName AS AdjustDisplayName
		 FROM dbo.GetAssignedBG(@employeeID) AS b
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS d ON b.BGID=d.BGID AND d.Active=1
		CROSS JOIN #tmpTable AS h
		WHERE b.Active=1 --AND (NOT EXISTS(SELECT TOP 1 e.PreContractID FROM dbo.vw_PreContractInfo AS e WHERE (e.PreContractAdjust IS NOT NULL) AND e.BDID=d.BDID))
	)
	DROP TABLE #tmpTable
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_2_5]    Script Date: 06/07/2011 10:11:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_2_5', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_2_5
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-07
-- Description:	This is for the monthly report 1.2.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_2_5]
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	--DECLARE @exchangeRate AS decimal(18,6)
	--SET @exchangeRate=dbo.FuncGetExchangeRate()
	--SET @exchangeRate=1000000*@exchangeRate
	(SELECT a.BGCode AS BGCode
		   ,a.BDCode AS BDCode
		   ,a.PreContractID AS PreContractID
		   --,a.OrionPrice/@exchangeRate AS OrionPrice
		   ,a.CEWPromised AS CEWPromised
		   ,a.CEWME AS SparePrice
		   ,a.OrionME AS OrionME
		   ,a.CFIPrice AS CFIPrice
		   --,a.SparePrice/@exchangeRate AS SparePrice
		   ,a.Delivered AS Delivered
		FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedBG(@employeeID) AS b ON b.Active=1 AND b.BGID=a.BGID
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS c ON c.Active=1 AND c.BDID=a.BDID
		WHERE a.AdjustID=21 and a.Delivered=1)
	UNION
	(SELECT a.BGCode AS BGCode
		   ,c.BDCode AS BDCode
		   ,NULL AS PreContractID
		   --,NULL AS OrionPrice
		   ,NULL AS CEWPromised
		   ,NULL AS SparePrice
		   ,NULL AS OrionME
		   ,NULL AS CFIPrice
		   --,NULL AS SparePrice
		   ,1 AS Delivered
		FROM dbo.GetAssignedBG(@employeeID) AS a
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS c ON c.Active=1 AND c.BGID=a.BGID
		WHERE a.Active=1 AND (NOT EXISTS(SELECT TOP 1 * FROM dbo.vw_PreContractInfo AS b WHERE b.AdjustID=21 AND b.Delivered=1 AND b.BDID=c.BDID)))
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_2_6]    Script Date: 06/07/2011 15:12:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_2_6', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_2_6
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-07
-- Description:	This is for monthly report 1.2.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_2_6] 
    @CurrentYear INT,
	@CurrentMonth INT,
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS decimal(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1000000*@exchangeRate
  	DECLARE @startTime AS date
  	DECLARE @beginTime AS date
  	SET @beginTime = '0001-01-01'
	SET @startTime=DATEADD(YEAR, @CurrentYear - 1, @beginTime)
	SET @startTime=DATEADD(MONTH, @CurrentMonth-1, @startTime)
	DECLARE @endTime AS date
	SET @endTime=DATEADD(year, @CurrentYear - 1, @beginTime)
	SET @endTime=DATEADD(month, @CurrentMonth, @endTime)
	SET @endTime=DATEADD(DAY, -1, @endTime)

	(SELECT a.[BGCode]
		   ,a.[BDCode]
		   ,SUM(b.[TransferMoney])/@exchangeRate AS TransferMoneySum
		   , @CurrentMonth AS [CurrentMonth]
		  FROM [dbo].[vw_PreContractInfo]  AS a
		  INNER JOIN [dbo].GetAssignedBG(@employeeID) AS c ON c.Active=1 AND c.BGID=a.BGID
		  INNER JOIN [dbo].GetAssignedBD(@employeeID) AS d ON d.BDID=a.BDID
		  INNER JOIN [dbo].[ContractTransfer] AS b ON a.PreContractID=b.PreContractID
		  WHERE a.BDActive=1 AND (b.TransferDate BETWEEN @startTime AND @endTime)
		  GROUP BY a.[BGCode], a.[BDCode])
  UNION
  (SELECT c.[BGCode] 
		 ,d.[BDCode]
		 ,NULL AS TransferMoneySum
		 , @CurrentMonth AS [CurrentMonth]
		 FROM [dbo].GetAssignedBG(@employeeID) AS c
		 INNER JOIN [dbo].GetAssignedBD(@employeeID) AS d ON d.Active=1 AND c.[BGID]=d.[BGID]
		 WHERE c.Active=1 AND (NOT EXISTS(SELECT TOP 1 e.PreContractID FROM dbo.vw_PreContractInfo AS e INNER JOIN dbo.ContractTransfer AS f ON f.PreContractID=e.PreContractID AND (f.TransferDate BETWEEN @startTime AND @endTime) WHERE e.BDID=d.BDID))
		 GROUP BY c.[BGCode], d.[BDCode])
  
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_3_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_3_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-08
-- Description:	This is for monthly report 1.3.
-- =============================================
CREATE PROCEDURE dbo.MonthlyReport1_3_1
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate AS decimal(18,6)
	CREATE TABLE #tmpTable (ExistMonth INT)
	INSERT INTO #tmpTable (ExistMonth)
	VALUES (0), (1), (4), (7), (10), (13)
	--SET @exchangeRate=100000*dbo.FuncGetExchangeRate()
	(SELECT b.ClusterEName AS ClusterEName
	       ,c.ProvinceEName AS ProvinceEName
	       ,a.ExistMonth AS ExistMonth
	       ,a.OrionME AS OrionME
	       ,a.CEWME AS SparePrice FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID)	AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.ProvinceActive=1 AND a.AdjustID=21)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,h.ExistMonth AS ExistMonth
	       ,NULL AS OrionME
	       ,NULL AS SparePrice FROM dbo.ProvinceCluster AS a
		 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		 CROSS JOIN #tmpTable AS h
		 WHERE a.IsActive=1 --AND (NOT EXISTS(SELECT TOP 1 * FROM dbo.vw_PreContractInfo AS c WHERE c.AdjustID=21 AND c.ProvinceID=b.ProvinceID))
	)
	DROP TABLE #tmpTable	
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_3_2]    Script Date: 06/08/2011 09:38:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_3_2', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_3_2
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-08
-- Description:	This is for monthly report 1.3.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_3_2]
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate AS decimal(18,6)
	--SET @exchangeRate=100000*dbo.FuncGetExchangeRate()
	CREATE TABLE #tmpTable (ExistMonth INT)
	INSERT INTO #tmpTable (ExistMonth)
	VALUES (0), (1), (4), (7), (10), (13)
	(SELECT a.CATCode AS CATCode
	       ,a.ExistMonth AS ExistMonth
	       ,a.OrionME AS OrionME
	       ,a.CEWME AS SparePrice FROM dbo.vw_PreContractInfo AS a
	    INNER JOIN dbo.GetAssignedCAT(@employeeID) as b ON a.CATID=b.CATID
		WHERE a.CATActive=1 AND a.AdjustID=21)
	UNION
	(SELECT a.CATCode
	       ,h.ExistMonth AS ExistMonth
	       ,NULL AS OrionME
	       ,NULL AS SparePrice 
	    FROM dbo.GetAssignedCAT(@employeeID) AS a
	    CROSS JOIN #tmpTable AS h 
		WHERE a.Active=1 --AND (NOT EXISTS(SELECT TOP 1 * FROM dbo.vw_PreContractInfo AS b WHERE b.AdjustID=21 AND b.CATID=a.CATID))
	)
	DROP TABLE #tmpTable
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_3_3]    Script Date: 06/08/2011 09:52:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_3_3', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_3_3
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-08
-- Description:	This is for monthly report 1.3.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_3_3]
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate AS decimal(18,6)
	--SET @exchangeRate=100000*dbo.FuncGetExchangeRate()
	CREATE TABLE #tmpTable (ExistMonth INT)
	INSERT INTO #tmpTable (ExistMonth)
	VALUES (0), (1), (4), (7), (10), (13)
	(SELECT b.BGCode AS BGCode
	      ,a.BDCode AS BDCode
	      ,a.ExistMonth AS ExistMonth
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS SparePrice FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedBG(@employeeID) AS b ON b.Active=1 AND a.BGID=b.BGID
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS c ON c.BDID=a.BDID
		WHERE a.BDActive=1 AND a.AdjustID=21)
	UNION
	(SELECT a.BGCode AS BGCode
	       ,b.BDCode AS BDCode
	       ,h.ExistMonth AS ExistMonth
	       ,0 AS OrionME
	       ,0 AS SparePrice FROM dbo.GetAssignedBG(@employeeID) AS a
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS b ON b.Active=1 AND b.BGID=a.BGID
		CROSS JOIN #tmpTable AS h
		WHERE a.Active=1 --AND (NOT EXISTS(SELECT TOP 1 * FROM dbo.vw_PreContractInfo AS c WHERE c.AdjustID=21 AND c.BDID=b.BDID))
	)
	DROP TABLE #tmpTable
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_5_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_5_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-08
-- Description:	This is for monthly report 1.5.
-- =============================================
CREATE PROCEDURE MonthlyReport1_5_1
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate DECIMAL(18,6)
	--SET @exchangeRate=1000000*dbo.FuncGetExchangeRate()
	(SELECT b.ClusterEName AS ClusterEName
	      ,d.ProvinceEName AS ProvinceEName
	      ,c.BGCode AS BGCode
	      ,a.BDCode AS BDCode
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS SparePrice
	      ,a.CFIPrice AS CFIPrice FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS d ON d.ProvinceID=a.ProvinceID
	INNER JOIN dbo.GetAssignedBG(@employeeID) AS c ON c.Active=1 AND a.BGID=c.BGID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.BDID=a.BDID
	WHERE a.ProvinceActive=1 AND a.BDActive=1 AND a.Delivered=1)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,g.BGCode AS BGCode
	       ,g.BDCode AS BDCode
	       ,NULL AS OrionME
	       ,NULL AS SparePrice
	       ,NULL AS CFIPrice
	        FROM dbo.ProvinceCluster AS a
		 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		 CROSS JOIN (SELECT d.BGCode, e.BDCode FROM dbo.GetAssignedBG(@employeeID) AS d
					 INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.Active=1 AND d.BGID=e.BGID
					 WHERE d.Active=1 AND (NOT EXISTS(SELECT TOP 1 f.PreContractID FROM dbo.vw_PreContractInfo AS f WHERE f.BGID=d.BGID AND f.BDID=e.BDID AND f.Delivered=1))) AS g
		 WHERE a.IsActive=1 AND 
			   (NOT EXISTS(SELECT top 1 c.PreContractID FROM dbo.vw_PreContractInfo AS c WHERE c.ProvinceID=b.ProvinceID 
						   AND c.ClusterID=a.ProvinceClusterID AND c.Delivered=1))				   
	)
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_5_2]    Script Date: 06/08/2011 13:17:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_5_2', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_5_2
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-08
-- Description:	This is for monthly report 1.5.1
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_5_2]
	@CATID INT,
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate decimal(18,6)
	--SET @exchangeRate=1000000*dbo.FuncGetExchangeRate()	
	(SELECT b.ClusterEName AS ClusterEName
	      ,d.ProvinceEName AS ProvinceEName
	      ,c.BGCode AS BGCode
	      ,a.BDCode AS BDCode
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS SparePrice
	      ,a.CFIPrice AS CFIPrice FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS d ON d.ProvinceID=a.ProvinceID
	INNER JOIN dbo.GetAssignedBG(@employeeID) AS c ON c.Active=1 AND a.BGID=c.BGID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.BDID=a.BDID
	WHERE a.ProvinceActive=1 AND a.BDActive=1 AND a.Delivered=1 AND a.CATID=@CATID)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,g.BGCode AS BGCode
	       ,g.BDCode AS BDCode
	       ,0 AS OrionME
	       ,0 AS SparePrice
	       ,0 AS CFIPrice
	        FROM dbo.ProvinceCluster AS a
	 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
	 CROSS JOIN (SELECT d.BGCode, e.BDCode FROM dbo.GetAssignedBG(@employeeID) AS d
	             INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.Active=1 AND d.BGID=e.BGID
	             WHERE d.Active=1 AND (NOT EXISTS(SELECT TOP 1 f.PreContractID FROM dbo.vw_PreContractInfo AS f WHERE f.BGID=d.BGID AND f.BDID=e.BDID AND f.Delivered=1 AND f.CATID=@CATID))) AS g
	 WHERE a.IsActive=1 AND 
		   (NOT EXISTS(SELECT top 1 c.PreContractID FROM dbo.vw_PreContractInfo AS c WHERE c.ProvinceID=b.ProvinceID 
					   AND c.ClusterID=a.ProvinceClusterID AND c.Delivered=1 AND c.CATID=@CATID))				   
	)
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport1_6_1]    Script Date: 06/08/2011 18:45:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport1_6_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport1_6_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-08
-- Description:	This is for monthly report 1.6.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport1_6_1]
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate decimal(18,6)
	--SET @exchangeRate=1000000*dbo.FuncGetExchangeRate()
	(SELECT b.ClusterEName AS ClusterEName
	      ,d.ProvinceEName AS ProvinceEName
	      ,a.CATCode AS CATCode
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS SparePrice
	      ,a.CFIPrice AS CFIPrice FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS d ON d.ProvinceID=a.ProvinceID
	INNER JOIN dbo.GetAssignedCAT(@employeeID) AS e ON e.CATID=a.CATID
	WHERE a.ProvinceActive=1 AND a.CATActive=1 AND a.Delivered=1)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,g.CATCode AS CATCode
	       ,NULL AS OrionME
	       ,NULL AS SparePrice
	       ,NULL AS CFIPrice
	        FROM dbo.ProvinceCluster AS a
	 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
	 CROSS JOIN (SELECT d.CATCode FROM dbo.GetAssignedCAT(@employeeID) AS d
	             WHERE d.Active=1 AND (NOT EXISTS(SELECT TOP 1 f.PreContractID FROM dbo.vw_PreContractInfo AS f WHERE f.CATID=d.CATID AND f.Delivered=1))) AS g
	 WHERE a.IsActive=1 AND 
		   (NOT EXISTS(SELECT TOP 1 c.PreContractID FROM dbo.vw_PreContractInfo AS c WHERE c.ProvinceID=b.ProvinceID 
					   AND c.ClusterID=a.ProvinceClusterID AND c.Delivered=1))				   
	)
END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport2_1_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport2_1_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-10
-- Description:	This is for monthly report 2.1.
-- =============================================
CREATE PROCEDURE dbo.MonthlyReport2_1_1 
    @CurrentYear INT,
	@CurrentMonth INT,
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startTime AS date
  	DECLARE @beginTime AS date
  	SET @beginTime = '0001-01-01'
	SET @startTime=DATEADD(YEAR, @CurrentYear - 1, @beginTime)
	SET @startTime=DATEADD(MONTH, @CurrentMonth - 1, @startTime)
	DECLARE @endTime AS date
	SET @endTime=DATEADD(year, @CurrentYear - 1, @beginTime)
	SET @endTime=DATEADD(month, @CurrentMonth, @endTime)
	SET @endTime=DATEADD(DAY, -1, @endTime)

	DECLARE @exchangeRate AS decimal(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1000000*@exchangeRate
	SELECT d.ProvinceEName
		  ,SUM(d.TransferMoney)/@exchangeRate AS ConversionAmt
		  ,SUM(d.TransferMoney*d.ConversionDays)/SUM(d.TransferMoney) AS ConversionLT FROM (SELECT c.ProvinceEName AS ProvinceEName
		   ,b.TransferMoney AS TransferMoney
		   ,DATEDIFF(day, a.ERPReceived, a.SignActual) AS ConversionDays FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ContractTransfer AS b ON b.PreContractID=a.PreContractID AND (b.TransferDate BETWEEN @startTime AND @endTime)
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.ProvinceID=a.ProvinceID
		WHERE a.ProvinceActive=1) AS d
		GROUP BY d.ProvinceEName
	UNION
	(SELECT a.ProvinceEName AS ProvinceEName
		   ,NULL AS ConversionLT
		   ,NULL AS ConversionAmt FROM dbo.GetAssignedProvince(@employeeID) AS a
		WHERE a.Active=1 AND (NOT EXISTS(SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b INNER JOIN dbo.ContractTransfer AS c ON c.PreContractID=b.PreContractID AND (c.TransferDate BETWEEN @startTime AND @endTime) WHERE b.ProvinceID=a.ProvinceID)))
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport2_2_1]    Script Date: 06/10/2011 14:12:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport2_2_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport2_2_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-10
-- Description:	This is for monthly report 2.2.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport2_2_1] 
    @CurrentYear INT,
	@CurrentMonth INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startTime AS date
  	DECLARE @beginTime AS date
  	SET @beginTime = '0001-01-01'
	SET @startTime=DATEADD(YEAR, @CurrentYear - 1, @beginTime)
	SET @startTime=DATEADD(MONTH, @CurrentMonth - 1, @startTime)
	DECLARE @endTime AS date
	SET @endTime=DATEADD(year, @CurrentYear - 1, @beginTime)
	SET @endTime=DATEADD(month, @CurrentMonth, @endTime)
	SET @endTime=DATEADD(DAY, -1, @endTime)

	DECLARE @exchangeRate AS decimal(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1000000*@exchangeRate
	SELECT d.ClusterEName AS ClusterEName
		  ,SUM(d.TransferMoney)/@exchangeRate AS ConversionAmt
		  ,SUM(d.TransferMoney*d.ConversionDays)/SUM(d.TransferMoney) AS ConversionLT FROM (SELECT e.ClusterEName AS ClusterEName
		   ,b.TransferMoney AS TransferMoney
		   ,DATEDIFF(day, a.ERPReceived, a.SignActual) AS ConversionDays FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS e ON a.ClusterID=e.ProvinceClusterID
		INNER JOIN dbo.ContractTransfer AS b ON b.PreContractID=a.PreContractID AND (b.TransferDate BETWEEN @startTime AND @endTime)
		WHERE e.IsActive=1) AS d
		GROUP BY d.ClusterEName
	UNION
	(SELECT a.ClusterEName AS ClusterEName
		   ,NULL AS ConversionLT
		   ,NULL AS ConversionAmt FROM dbo.ProvinceCluster AS a
		WHERE a.IsActive=1 AND (NOT EXISTS(SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b INNER JOIN dbo.ContractTransfer AS c ON b.PreContractID=c.PreContractID AND (c.TransferDate BETWEEN @startTime AND @endTime) WHERE b.ClusterID=a.ProvinceClusterID)))
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport2_3_1]    Script Date: 06/10/2011 14:35:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport2_3_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport2_3_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-10
-- Description:	This is for monthly report 2.3.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport2_3_1] 
    @CurrentYear INT,
	@CurrentMonth INT,
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startTime AS date
  	DECLARE @beginTime AS date
  	SET @beginTime = '0001-01-01'
	SET @startTime=DATEADD(YEAR, @CurrentYear - 1, @beginTime)
	SET @startTime=DATEADD(MONTH, @CurrentMonth - 1, @startTime)
	DECLARE @endTime AS date
	SET @endTime=DATEADD(year, @CurrentYear - 1, @beginTime)
	SET @endTime=DATEADD(month, @CurrentMonth, @endTime)
	SET @endTime=DATEADD(DAY, -1, @endTime)

	DECLARE @exchangeRate AS decimal(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1000000*@exchangeRate
	SELECT d.CATCode AS CATCode
		  ,SUM(d.TransferMoney)/@exchangeRate AS ConversionAmt
		  ,SUM(d.TransferMoney*d.ConversionDays)/SUM(d.TransferMoney) AS ConversionLT FROM (SELECT a.CATCode AS CATCode
		   ,b.TransferMoney AS TransferMoney
		   ,DATEDIFF(day, a.ERPReceived, a.SignActual) AS ConversionDays FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS e ON e.CATID=a.CATID
		INNER JOIN dbo.ContractTransfer AS b ON b.PreContractID=a.PreContractID AND (b.TransferDate BETWEEN @startTime AND @endTime)
		WHERE a.CATActive=1) AS d
		GROUP BY d.CATCode
	UNION
	(SELECT a.CATCode AS CATCode
		   ,NULL AS ConversionLT
		   ,NULL AS ConversionAmt FROM dbo.GetAssignedCAT(@employeeID) AS a
		WHERE a.Active=1 AND (NOT EXISTS(SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b INNER JOIN dbo.ContractTransfer AS c ON b.PreContractID=c.PreContractID AND (c.TransferDate BETWEEN @startTime AND @endTime) WHERE b.CATCode=a.CATCode)))
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport2_4_1]    Script Date: 06/10/2011 15:01:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport2_4_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport2_4_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-10
-- Description:	This is for monthly report 2.4.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport2_4_1] 
    @CurrentYear INT,
	@CurrentMonth INT,
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startTime AS date
  	DECLARE @beginTime AS date
  	SET @beginTime = '0001-01-01'
	SET @startTime=DATEADD(YEAR, @CurrentYear - 1, @beginTime)
	SET @startTime=DATEADD(MONTH, @CurrentMonth - 1, @startTime)
	DECLARE @endTime AS date
	SET @endTime=DATEADD(year, @CurrentYear - 1, @beginTime)
	SET @endTime=DATEADD(month, @CurrentMonth, @endTime)
	SET @endTime=DATEADD(DAY, -1, @endTime)

	DECLARE @exchangeRate AS decimal(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1000000*@exchangeRate
	SELECT d.BGCode AS BGCode
	      ,d.BDCode AS BDCode
		  ,SUM(d.TransferMoney)/@exchangeRate AS ConversionAmt
		  ,SUM(d.TransferMoney*d.ConversionDays)/SUM(d.TransferMoney) AS ConversionLT FROM (SELECT e.BGCode AS BGCode
		   ,a.BDCode AS BDCode
		   ,b.TransferMoney AS TransferMoney
		   ,DATEDIFF(day, a.ERPReceived, a.SignActual) AS ConversionDays FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedBG(@employeeID) AS e ON e.Active=1 AND e.BGID=a.BGID
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS f ON f.BDID=a.BDID
		INNER JOIN dbo.ContractTransfer AS b ON b.PreContractID=a.PreContractID AND (b.TransferDate BETWEEN @startTime AND @endTime)
		WHERE a.BDActive=1) AS d
		GROUP BY d.BGCode,d.BDCode
	UNION
	(SELECT a.BGCode AS BGCode
	       ,d.BDCode AS BDCode
		   ,NULL AS ConversionLT
		   ,NULL AS ConversionAmt FROM dbo.GetAssignedBG(@employeeID) AS a
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS d ON d.Active=1 AND d.BGID=a.BGID
		WHERE a.Active=1 AND (NOT EXISTS(SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b INNER JOIN dbo.ContractTransfer AS c ON b.PreContractID=c.PreContractID AND (c.TransferDate BETWEEN @startTime AND @endTime) WHERE b.BGID=a.BGID AND b.BDID=d.BDID)))
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport2_5_1]    Script Date: 06/10/2011 15:19:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport2_5_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport2_5_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-10
-- Description:	This is for monthly report 2.5.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport2_5_1] 
    @CurrentYear INT,
	@CurrentMonth INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startTime AS date
  	DECLARE @beginTime AS date
  	SET @beginTime = '0001-01-01'
	SET @startTime=DATEADD(YEAR, @CurrentYear - 1, @beginTime)
	SET @startTime=DATEADD(MONTH, @CurrentMonth - 1, @startTime)
	DECLARE @endTime AS date
	SET @endTime=DATEADD(year, @CurrentYear - 1, @beginTime)
	SET @endTime=DATEADD(month, @CurrentMonth, @endTime)
	SET @endTime=DATEADD(DAY, -1, @endTime)

	DECLARE @exchangeRate AS decimal(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1000000*@exchangeRate
	CREATE TABLE #Tmp (ContractTypeID INT, TransferMoney FLOAT, ConversionDays INT)
	INSERT INTO #Tmp(ContractTypeID,TransferMoney,ConversionDays) 
	(SELECT A.TypeID AS ContractTypeID
		   ,b.TransferMoney AS TransferMoney
		   ,ABS(DATEDIFF(day, a.ERPReceived, a.SignActual)) AS ConversionDays FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.DicItem AS e ON e.DicItemID=a.TypeID
		INNER JOIN dbo.ContractTransfer AS b ON b.PreContractID=a.PreContractID AND (b.TransferDate BETWEEN @startTime AND @endTime)
		WHERE e.IsEnabled=1 AND e.DicTypeID=2)
	
	SELECT i.DicItemValue AS ContractType
	      ,h.ConversionLT AS ConversionLT
	      ,h.ConversionAmt AS ConversionAmt FROM dbo.DicItem AS i
		LEFT OUTER JOIN (SELECT d.ContractTypeID AS ContractTypeID
		  ,SUM(d.TransferMoney)/@exchangeRate AS ConversionAmt
		  ,SUM(d.TransferMoney*d.ConversionDays)/SUM(d.TransferMoney) AS ConversionLT FROM #Tmp AS d
		GROUP BY d.ContractTypeID
		HAVING SUM(d.TransferMoney)<>0) AS h ON H.ContractTypeID=i.DicItemID
	WHERE i.IsEnabled=1 AND i.DicTypeID=2
	DROP TABLE #Tmp
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport3_1_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport3_1_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-09
-- Description:	This is for monthly report 3.1.
-- =============================================
CREATE PROCEDURE dbo.MonthlyReport3_1_1 
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	(SELECT c.DicItemValue AS ProblemLV1
	      ,d.DicItemValue AS ProblemLV2
	      ,a.CATCode AS CATCode
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS SparePrice
	      ,a.CFIPrice AS CFIPrice FROM dbo.SalesFeedback AS b
	      INNER JOIN dbo.DicItem AS c ON b.ProblemLV1=c.DicItemID
	      INNER JOIN dbo.DicItem AS d ON b.ProblemLV2=d.DicItemID
		  INNER JOIN dbo.vw_PreContractInfo AS a ON b.PreContractID=a.PreContractID
		  INNER JOIN dbo.GetAssignedCAT(@employeeID) AS e ON e.CATID=a.CATID
	WHERE a.CATActive=1 AND a.Delivered=1 AND (a.StatusID IN (10,11)))
	UNION
	(SELECT k.ProblemLV1 AS ProblemLV1
	       ,k.ProblemLV2 AS ProblemLV2
	       ,a.CATCode AS CATCode
	       ,NULL AS OrionME
	       ,NULL AS SparePrice
	       ,NULL AS CFIPrice FROM dbo.GetAssignedCAT(@employeeID) AS a
	     CROSS JOIN (SELECT i.DicItemValue AS ProblemLV1
						   ,j.DicItemValue AS ProblemLV2 FROM dbo.ProblemLVAssignment AS h
						INNER JOIN dbo.DicItem AS i ON i.IsEnabled=1 AND i.DicItemID=h.ProblemLV1ID
						INNER JOIN dbo.DicItem AS j ON j.IsEnabled=1 AND j.DicItemID=h.ProblemLV2ID) AS k
		 WHERE a.Active=1 AND (NOT EXISTS(SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b 
											INNER JOIN dbo.SalesFeedback AS c ON c.PreContractID=b.PreContractID
											WHERE b.Delivered=1 AND (b.StatusID IN (10,11)) AND b.CATID=a.CATID)))
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport3_2_1]    Script Date: 06/09/2011 14:38:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport3_2_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport3_2_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-09
-- Description:	This is for monthly report 3.2.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport3_2_1] 
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate AS DECIMAL(18,6)
	--SET @exchangeRate=1000000*dbo.FuncGetExchangeRate()
	(SELECT c.DicItemValue AS ProblemLV1
	       ,d.DicItemValue AS ProblemLV2
	       ,a.BGCode AS BGCode
	       ,a.BDCode AS BDCode
	       ,a.OrionME AS OrionME
	       ,a.CEWME AS SparePrice
	       ,a.CFIPrice AS CFIPrice FROM dbo.SalesFeedback AS b
	      INNER JOIN dbo.DicItem AS c ON b.ProblemLV1=c.DicItemID
	      INNER JOIN dbo.DicItem AS d ON b.ProblemLV2=d.DicItemID
		  INNER JOIN dbo.vw_PreContractInfo AS a ON b.PreContractID=a.PreContractID
		  INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.BDID=a.BDID
		  WHERE a.BDActive=1 AND a.Delivered=1 AND (a.StatusID IN (10,11)))
	UNION
	(SELECT k.ProblemLV1 AS ProblemLV1
	       ,k.ProblemLV2 AS ProblemLV2
	       ,a.BGCode AS BGCode
	       ,d.BDCode AS BDCode
	       ,NULL AS OrionME
	       ,NULL AS SparePrice
	       ,NULL AS CFIPrice FROM dbo.GetAssignedBG(@employeeID) AS a
		 INNER JOIN dbo.GetAssignedBD(@employeeID) AS d ON d.Active=1 AND d.BGID=a.BGID
		 CROSS JOIN (SELECT i.DicItemValue AS ProblemLV1
						   ,j.DicItemValue AS ProblemLV2 FROM dbo.ProblemLVAssignment AS h
						INNER JOIN dbo.DicItem AS i ON i.IsEnabled=1 AND i.DicItemID=h.ProblemLV1ID
						INNER JOIN dbo.DicItem AS j ON j.IsEnabled=1 AND j.DicItemID=h.ProblemLV2ID) AS k
		 WHERE a.Active=1 AND (NOT EXISTS(SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b 
											INNER JOIN dbo.SalesFeedback AS c ON c.PreContractID=b.PreContractID
											WHERE b.Delivered=1 AND (b.StatusID IN (10,11)) AND b.BDID=d.BDID)))
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport3_3_1]    Script Date: 06/09/2011 15:39:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport3_3_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport3_3_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-09
-- Description:	This is for monthly report 3.3.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport3_3_1] 
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate AS DECIMAL(18,6)
	--SET @exchangeRate=1000000*dbo.FuncGetExchangeRate()
	(SELECT c.DicItemValue AS ProblemLV1
	      ,d.DicItemValue AS ProblemLV2
	      ,e.ClusterEName AS ClusterEName
	      ,f.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS SparePrice
	      ,a.CFIPrice AS CFIPrice FROM dbo.SalesFeedback AS b
	      INNER JOIN dbo.DicItem AS c ON b.ProblemLV1=c.DicItemID
	      INNER JOIN dbo.DicItem AS d ON b.ProblemLV2=d.DicItemID
		  INNER JOIN dbo.vw_PreContractInfo AS a ON b.PreContractID=a.PreContractID
		  INNER JOIN dbo.ProvinceCluster AS e ON e.IsActive=1 AND e.ProvinceClusterID=a.ClusterID
		  INNER JOIN dbo.GetAssignedProvince(@employeeID) AS f ON f.ProvinceID=a.ProvinceID
		  WHERE a.ProvinceActive=1 AND a.Delivered=1 AND (a.StatusID IN (10,11)))
	UNION
	(SELECT h.ProblemLV1 AS ProblemLV1
	       ,h.ProblemLV2 AS ProblemLV2
	       ,a.ClusterEName AS ClusterEName
	       ,d.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS SparePrice
	       ,NULL AS CFIPrice FROM dbo.ProvinceCluster AS a
		 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS d ON d.Active=1 AND d.ClusterID=a.ProvinceClusterID
		 CROSS JOIN (SELECT b.DicItemValue AS ProblemLV1
						   ,c.DicItemValue AS ProblemLV2 FROM dbo.ProblemLVAssignment AS a
						 INNER JOIN dbo.DicItem AS b ON b.IsEnabled=1 AND a.ProblemLV1ID=b.DicItemID
						 INNER JOIN dbo.DicItem AS c ON c.IsEnabled=1 AND a.ProblemLV2ID=c.DicItemID) AS h
		 WHERE a.IsActive=1 AND (NOT EXISTS(SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b 
										INNER JOIN dbo.SalesFeedback AS c ON c.PreContractID=b.PreContractID
										WHERE b.Delivered=1 AND (b.StatusID IN (10,11)) AND b.ProvinceID=d.ProvinceID)))
	
END
GO

/****** Object:  StoredProcedure [dbo].[MonthlyReport3_3_1_1]    Script Date: 06/09/2011 16:16:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport3_3_1_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport3_3_1_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-09
-- Description:	This is for monthly report 3.3.1.
-- =============================================
CREATE PROCEDURE [dbo].[MonthlyReport3_3_1_1] 
	@catID INT,
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	--DECLARE @exchangeRate AS DECIMAL(18,6)
	--SET @exchangeRate=1000000*dbo.FuncGetExchangeRate()
	(SELECT c.DicItemValue AS ProblemLV1
	      ,d.DicItemValue AS ProblemLV2
	      ,e.ClusterEName AS ClusterEName
	      ,f.ProvinceCode AS ProvinceCode
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS SparePrice
	      ,a.CFIPrice AS CFIPrice FROM dbo.SalesFeedback AS b
	      INNER JOIN dbo.DicItem AS c ON b.ProblemLV1=c.DicItemID
	      INNER JOIN dbo.DicItem AS d ON b.ProblemLV2=d.DicItemID
		  INNER JOIN dbo.vw_PreContractInfo AS a ON b.PreContractID=a.PreContractID
		  INNER JOIN dbo.ProvinceCluster AS e ON e.IsActive=1 AND e.ProvinceClusterID=a.ClusterID
		  INNER JOIN dbo.GetAssignedProvince(@employeeID) AS f ON f.ProvinceID=a.ProvinceID
		  INNER JOIN dbo.GetAssignedCAT(@employeeID) AS g ON g.CATID=a.CATID
	WHERE a.ProvinceActive=1 AND a.CATID=@catID AND a.Delivered=1 AND (a.StatusID IN (10,11)))
	UNION
	(SELECT k.ProblemLV1 AS ProblemLV1
	       ,k.ProblemLV2 AS ProblemLV2
	       ,a.ClusterEName AS ClusterEName
	       ,d.ProvinceCode AS ProvinceCode
	       ,NULL AS OrionME
	       ,NULL AS SparePrice
	       ,NULL AS CFIPrice FROM dbo.ProvinceCluster AS a
	 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS d ON d.Active=1 AND d.ClusterID=a.ProvinceClusterID
	 CROSS JOIN (SELECT i.DicItemValue AS ProblemLV1
					   ,j.DicItemValue AS ProblemLV2 FROM dbo.ProblemLVAssignment AS h
					 INNER JOIN dbo.DicItem AS i ON i.IsEnabled=1 AND h.ProblemLV1ID=i.DicItemID
					 INNER JOIN dbo.DicItem AS j ON j.IsEnabled=1 AND h.ProblemLV2ID=j.DicItemID) AS k
	 WHERE a.IsActive=1 AND (NOT EXISTS(SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b 
										INNER JOIN dbo.SalesFeedback AS c ON c.PreContractID=b.PreContractID
										WHERE b.Delivered=1 AND (b.StatusID IN (10,11)) AND b.ProvinceID=d.ProvinceID AND b.CATID=@catID)))
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport3_4_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport3_4_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-15
-- Description:	This is for monthly report 3.4.
-- =============================================
CREATE PROCEDURE dbo.MonthlyReport3_4_1 
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS DECIMAL(18,6)
	SET @exchangeRate=1000000*dbo.FuncGetExchangeRate()
	CREATE TABLE #tmpTable(CATCode NVARCHAR(10), Duration INT, CEWME DECIMAL(38,17), OrionME DECIMAL(38,17))
	INSERT INTO #tmpTable(CATCode, Duration, CEWME, OrionME)
	(SELECT a.CATCode
		  ,ABS(DATEDIFF(DAY, a.SignActual, a.ERPReceived)) AS Duration
		  ,a.CEWME AS CEWME
		  ,a.OrionME FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.GetAssignedCAT(@employeeID) AS b ON b.Active=1 AND a.CATID=b.CATID
	WHERE a.Delivered=1)
	SELECT h.CATCode
	      ,SUM(h.Duration*h.CEWME)/SUM(h.CEWME) AS Duration
	      ,SUM(CEWME) AS CEWMESum
	      ,SUM(OrionME) AS OrionMESum FROM #tmpTable AS h
	GROUP BY h.CATCode
	DROP TABLE #tmpTable
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MonthlyReport3_4_2', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MonthlyReport3_4_2
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-07
-- Description:	This is for monthly report 3.4.
-- =============================================
CREATE PROCEDURE dbo.MonthlyReport3_4_2
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	CREATE TABLE #tmpTable (ProvinceEName NVARCHAR(100), Duration INT, CEWME DECIMAL(38,17), OrionME DECIMAL(38,17))
	INSERT INTO #tmpTable (ProvinceEName, Duration, CEWME, OrionME)
	(SELECT b.ProvinceEName AS ProvinceEName
				 ,ABS(DATEDIFF(DAY, a.SignActual, a.ERPReceived)) AS Duration
				 ,a.CEWME AS CEWME
				 ,a.OrionME AS OrionME FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND a.ProvinceID=b.ProvinceID
	WHERE a.Delivered=1)
	SELECT TOP 10 h.ProvinceEName AS ProvinceEName
	      ,SUM(h.Duration*h.CEWME)/SUM(h.CEWME) AS Duration
	      ,SUM(h.CEWME) AS CEWMESum
	      ,SUM(h.OrionME) AS OrionMESum FROM #tmpTable AS h
	      GROUP BY h.ProvinceEName
	      ORDER BY Duration DESC
	DROP TABLE #tmpTable
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MontlyReport2_6_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MontlyReport2_6_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-10
-- Description:	This is for monthly report 2.6.
-- =============================================
CREATE PROCEDURE dbo.MontlyReport2_6_1
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS DECIMAL(18,6)
	SET @exchangeRate=1000000*dbo.FuncGetExchangeRate()
	SELECT c.DicItemValue AS PreContractType
	      ,d.DicItemValue AS PreContractCatalog
	      ,e.DicItemValue AS PreContractStatus
	      ,a.PreContractID AS PreContractID
	      ,a.BDCode
	      ,a.BGCode
	      ,a.PreContractNo
	      ,a.ProductName
	      ,a.ClusterCName
	      ,a.ProvinceCName
	      ,a.CATCode
	      ,b.TransferMoney
	      ,b.TransferMoney/@exchangeRate AS TransferMoneyME
	      ,a.SignActual
	      ,a.ERPReceived
	      ,a.Delivered
	      ,a.DeliveryDate
	      ,b.ContractNo
	      ,a.OrionNo
	      ,ABS(DATEDIFF(day,a.SignActual,a.DeliveryDate)) AS TransformationDays FROM dbo.vw_PreContractInfo AS a
	    INNER JOIN dbo.GetAssignedBG(@employeeID) AS h ON h.BGID=a.BGID
	    INNER JOIN dbo.GetAssignedBD(@employeeID) AS i ON i.BDID=a.BDID
	    INNER JOIN dbo.GetAssignedCAT(@employeeID) AS j ON j.CATID=a.CATID
	    INNER JOIN dbo.GetAssignedProvince(@employeeID) AS k ON k.ProvinceID=a.ProvinceID
		INNER JOIN dbo.ContractTransfer AS b ON a.PreContractID=b.PreContractID
		INNER JOIN dbo.DicItem AS c ON a.TypeID=c.DicItemID
		INNER JOIN dbo.DicItem AS d ON a.AdjustID=d.DicItemID
		INNER JOIN dbo.DicItem AS e ON a.TypeID=e.DicItemID
		WHERE a.BDActive=1 AND a.ProvinceActive=1 AND a.CATActive=1
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MontlyReport5_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MontlyReport5_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-16
-- Description:	This is for monthly report 5.
-- =============================================
CREATE PROCEDURE dbo.MontlyReport5_1 
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT s.DicItemValue AS ProjectCatalog
	      ,q.CATCode AS CATCode
	      ,r.ProvinceEName AS ProvinceEName
	      ,u.CEWMESum AS ContractSum
	      ,v.CEWMESum AS DeliveredSum
	      ,w.CEWMESum AS LessThanOneSum
	      ,w1.CEWMESum AS OneToThreeSum
	      ,w2.CEWMESum AS FourToSixSum
	      ,w3.CEWMESum AS SevenToNineSum
	      ,w4.CEWMESum AS TenToTwelveSum
	      ,w5.CEWMESum AS MoreThanTwelveSum FROM dbo.DicItem AS s 
	CROSS JOIN dbo.GetAssignedCAT(@employeeID) AS q 
	CROSS JOIN dbo.GetAssignedProvince(@employeeID) AS r
	LEFT OUTER JOIN 
	(SELECT b.DashboardIssueType AS ProjectCatalogID
			  ,h.CATID AS CATID
			  ,i.ProvinceID AS ProvinceID
			  ,SUM(a.CEWME) AS CEWMESum FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS h ON h.Active=1 AND a.CATID=h.CATID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND a.ProvinceID=i.ProvinceID
		INNER JOIN dbo.SalesFeedback AS b ON a.PreContractID=b.PreContractID
		WHERE a.AdjustID=21
		GROUP BY b.DashboardIssueType,h.CATID,i.ProvinceID) AS u ON u.ProjectCatalogID=s.DicItemID AND u.CATID=q.CATID AND u.ProvinceID=r.ProvinceID
	LEFT OUTER JOIN
	(SELECT b.DashboardIssueType AS ProjectCatalogID
			  ,h.CATID AS CATID
			  ,i.ProvinceID AS ProvinceID
			  ,SUM(a.CEWME) AS CEWMESum FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS h ON h.Active=1 AND a.CATID=h.CATID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND a.ProvinceID=i.ProvinceID
		INNER JOIN dbo.SalesFeedback AS b ON a.PreContractID=b.PreContractID
		WHERE a.Delivered=1 AND a.AdjustID=21
		GROUP BY b.DashboardIssueType,h.CATID,i.ProvinceID) AS v ON v.ProjectCatalogID=s.DicItemID AND v.CATID=q.CATID AND v.ProvinceID=r.ProvinceID
	LEFT OUTER JOIN
	(SELECT b.DashboardIssueType AS ProjectCatalogID
			  ,h.CATID AS CATID
			  ,i.ProvinceID AS ProvinceID
			  ,SUM(a.CEWME) AS CEWMESum FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS h ON h.Active=1 AND a.CATID=h.CATID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND a.ProvinceID=i.ProvinceID
		INNER JOIN dbo.SalesFeedback AS b ON a.PreContractID=b.PreContractID
		WHERE a.Delivered=1 AND a.AdjustID=21 AND a.ExistMonth < 1
		GROUP BY b.DashboardIssueType,h.CATID,i.ProvinceID) AS w ON w.ProjectCatalogID=s.DicItemID AND w.CATID=q.CATID AND w.ProvinceID=r.ProvinceID
	LEFT OUTER JOIN
	(SELECT b.DashboardIssueType AS ProjectCatalogID
			  ,h.CATID AS CATID
			  ,i.ProvinceID AS ProvinceID
			  ,SUM(a.CEWME) AS CEWMESum FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS h ON h.Active=1 AND a.CATID=h.CATID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND a.ProvinceID=i.ProvinceID
		INNER JOIN dbo.SalesFeedback AS b ON a.PreContractID=b.PreContractID
		WHERE a.Delivered=1 AND a.AdjustID=21 AND (a.ExistMonth BETWEEN 1 AND 3)
		GROUP BY b.DashboardIssueType,h.CATID,i.ProvinceID) AS w1 ON w1.ProjectCatalogID=s.DicItemID AND w1.CATID=q.CATID AND w1.ProvinceID=r.ProvinceID
	LEFT OUTER JOIN
	(SELECT b.DashboardIssueType AS ProjectCatalogID
			  ,h.CATID AS CATID
			  ,i.ProvinceID AS ProvinceID
			  ,SUM(a.CEWME) AS CEWMESum FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS h ON h.Active=1 AND a.CATID=h.CATID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND a.ProvinceID=i.ProvinceID
		INNER JOIN dbo.SalesFeedback AS b ON a.PreContractID=b.PreContractID
		WHERE a.Delivered=1 AND a.AdjustID=21 AND (a.ExistMonth BETWEEN 4 AND 6)
		GROUP BY b.DashboardIssueType,h.CATID,i.ProvinceID) AS w2 ON w2.ProjectCatalogID=s.DicItemID AND w2.CATID=q.CATID AND w2.ProvinceID=r.ProvinceID
	LEFT OUTER JOIN
	(SELECT b.DashboardIssueType AS ProjectCatalogID
			  ,h.CATID AS CATID
			  ,i.ProvinceID AS ProvinceID
			  ,SUM(a.CEWME) AS CEWMESum FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS h ON h.Active=1 AND a.CATID=h.CATID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND a.ProvinceID=i.ProvinceID
		INNER JOIN dbo.SalesFeedback AS b ON a.PreContractID=b.PreContractID
		WHERE a.Delivered=1 AND a.AdjustID=21 AND (a.ExistMonth BETWEEN 7 AND 9)
		GROUP BY b.DashboardIssueType,h.CATID,i.ProvinceID) AS w3 ON w3.ProjectCatalogID=s.DicItemID AND w3.CATID=q.CATID AND w3.ProvinceID=r.ProvinceID
	LEFT OUTER JOIN
	(SELECT b.DashboardIssueType AS ProjectCatalogID
			  ,h.CATID AS CATID
			  ,i.ProvinceID AS ProvinceID
			  ,SUM(a.CEWME) AS CEWMESum FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS h ON h.Active=1 AND a.CATID=h.CATID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND a.ProvinceID=i.ProvinceID
		INNER JOIN dbo.SalesFeedback AS b ON a.PreContractID=b.PreContractID
		WHERE a.Delivered=1 AND a.AdjustID=21 AND (a.ExistMonth BETWEEN 10 AND 12)
		GROUP BY b.DashboardIssueType,h.CATID,i.ProvinceID) AS w4 ON w4.ProjectCatalogID=s.DicItemID AND w4.CATID=q.CATID AND w4.ProvinceID=r.ProvinceID
	LEFT OUTER JOIN
	(SELECT b.DashboardIssueType AS ProjectCatalogID
			  ,h.CATID AS CATID
			  ,i.ProvinceID AS ProvinceID
			  ,SUM(a.CEWME) AS CEWMESum FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS h ON h.Active=1 AND a.CATID=h.CATID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND a.ProvinceID=i.ProvinceID
		INNER JOIN dbo.SalesFeedback AS b ON a.PreContractID=b.PreContractID
		WHERE a.Delivered=1 AND a.AdjustID=21 AND (a.ExistMonth>12)
		GROUP BY b.DashboardIssueType,h.CATID,i.ProvinceID) AS w5 ON w5.ProjectCatalogID=s.DicItemID AND w5.CATID=q.CATID AND w5.ProvinceID=r.ProvinceID
	WHERE s.IsEnabled=1 AND s.DicTypeID=12
END
GO

/****** Object:  StoredProcedure [dbo].[MontlyReport6_1]    Script Date: 06/13/2011 10:39:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MontlyReport6_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.MontlyReport6_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-10
-- Description:	This is for monthly report 2.6.
-- =============================================
CREATE PROCEDURE [dbo].[MontlyReport6_1]
	@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS DECIMAL(18,6)
	SET @exchangeRate=1000000*dbo.FuncGetExchangeRate()
	SELECT c.DicItemValue AS PreContractType
	      ,d.DicItemValue AS PreContractCatalog
	      ,e.DicItemValue AS PreContractStatus
	      ,a.PreContractNo AS PreContractNo
	      ,a.BDCode
	      ,a.BGCode
	      ,f.DeadlineOfTrying
	      ,a.ContractName
	      ,a.ProductName
	      ,a.ClusterCName
	      ,a.ProvinceCName
	      ,g.ProvinceEName
	      ,a.CATCode
	      ,a.CEWPromised/@exchangeRate AS CEWPromisedME
	      ,a.CEWME
	      ,a.SignActual
	      ,a.OrionME
	      ,a.SignDue
	      ,a.TradeMonth
	      ,a.CFIPrice AS CFIME
	      ,a.ERPReceived
	      ,DATEDIFF(DAY, a.ERPReceived,GETDATE()) AS ExistingDays
	      ,a.SpecialApprove
	      ,DATEDIFF(MONTH, a.ERPReceived,GETDATE()) AS ExistingMonths
	      ,a.Delivered
	      ,a.DeliveryPercent
	      ,a.DeliveryDate
	      ,a.DeliveryPlan
	      ,DATEDIFF(MONTH, a.ERPReceived,GETDATE()) AS Aging
	      ,n.DicItemValue AS SignType
	      ,i.DicItemValue AS TreatmentWay
	      ,NULL AS SignToCancelTryLeasePrecontract --cancel of try/lease precontract signment
	      ,j.DicItemValue AS CancelProcess
	      ,MONTH(h.OperationDate) AS ProcessingMonth
	      ,k.DicItemValue AS ProblemLV1
	      ,m.DicItemValue AS ProblemLV2
	      ,NULL AS ProjectCatalog--project catalog
	      ,NULL AS NeedSupportFromCompany --need company support
	      ,h.PeopleInCharge
	      ,h.Comments
	      FROM dbo.vw_PreContractInfo AS a
	    INNER JOIN dbo.PreContractInfo AS f ON f.PreContractID=a.PreContractID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS g ON a.ProvinceID=g.ProvinceID
		INNER JOIN dbo.GetAssignedBG(@employeeID) AS u ON u.BGID=a.BGID
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS v ON v.BDID=a.BDID
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS w ON w.CATID=a.CATID
		INNER JOIN dbo.DicItem AS c ON a.TypeID=c.DicItemID
		INNER JOIN dbo.DicItem AS d ON a.AdjustID=d.DicItemID
		INNER JOIN dbo.DicItem AS e ON a.TypeID=e.DicItemID
		LEFT OUTER JOIN dbo.ContractTransfer AS b ON a.PreContractID=b.PreContractID
		LEFT OUTER JOIN dbo.SalesFeedback AS h ON h.PreContractID=a.PreContractID
		LEFT OUTER JOIN dbo.DicItem AS n ON n.DicItemID=h.SignType
		LEFT OUTER JOIN dbo.DicItem AS i ON i.DicItemID=h.TreatmentWay
		LEFT OUTER JOIN dbo.DicItem AS j ON j.DicItemID=h.CancelProcess
		LEFT OUTER JOIN dbo.DicItem AS k ON k.DicItemID=h.ProblemLV1
		LEFT OUTER JOIN dbo.DicItem AS m ON m.DicItemID=h.ProblemLV2
		LEFT OUTER JOIN dbo.DicItem AS p ON p.DicItemID=h.InvoiceStatus
		WHERE a.CATActive=1 AND a.BDActive=1 AND a.ProvinceActive=1
END
GO

-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-10
-- Description:	This is for creating snapshot.
-- =============================================
IF OBJECT_ID('dbo.SPCreateSnapshot', 'P') IS NOT NULL
	DROP PROCEDURE dbo.SPCreateSnapshot
GO
CREATE PROCEDURE dbo.SPCreateSnapshot 
	@snapshotName AS NVARCHAR(30),
	@logicalFileName AS NVARCHAR(30),
	@fileStreamPath AS NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql AS NVARCHAR(500)
SET @sql='IF EXISTS(SELECT sdb.name FROM sys.databases AS sdb WHERE sdb.name=''' + @snapshotName + ''')
BEGIN
	DROP DATABASE ' + @snapshotName + '
END
CREATE DATABASE ' + @snapshotName + ' ON
(NAME=' + @logicalFileName + ', FILENAME=''' + @fileStreamPath + ''')
AS SNAPSHOT OF PreContract3'
EXECUTE(@sql)
END
GO

-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-10
-- Description:	This is for deleting snapshot.
-- =============================================
IF OBJECT_ID('dbo.SPDeleteSnapshot', 'P') IS NOT NULL
	DROP PROCEDURE dbo.SPDeleteSnapshot
GO
CREATE PROCEDURE dbo.SPDeleteSnapshot
	@snapshotName AS NVARCHAR(30)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @sql AS NVARCHAR(300)
	SET @sql='IF EXISTS(SELECT sdb.name FROM sys.databases AS sdb WHERE sdb.name=''' + @snapshotName + ''')
BEGIN
	DROP DATABASE ' + @snapshotName + '
END'
	EXECUTE (@sql)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_1_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_1_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-15
-- Description:	This is for weekly report 1.1.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport1_1_1 
	@startTime DATE
   ,@endTime DATE
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS decimal(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1000000*@exchangeRate
	CREATE TABLE #tmpTable(AdjustDisplayName NVARCHAR(15))
	INSERT INTO #tmpTable (AdjustDisplayName)
	VALUES ('Pre-'),('Test/Lease'),('Try&Buy')
	(SELECT
	  b.ClusterEName AS ClusterEName
	  ,c.ProvinceEName AS ProvinceEName
	  ,a.PreContractID AS PreContractID
	  ,a.OrionPrice AS OrionPrice
	  ,a.CEWME AS CEWME
	  ,a.OrionME AS OrionME
	  ,a.CFIPrice AS CFIPrice
	  ,a.SparePrice/@exchangeRate AS SparePrice
	  ,CASE a.AdjustID WHEN 21 THEN 'Pre-'
		WHEN 18 THEN 'Test/Lease'
		WHEN 20 THEN 'Test/Lease'
		WHEN 19 THEN 'Try&Buy'
		ELSE 'Others' END AS AdjustDisplayName
	FROM
	  vw_PreContractInfo AS a
	INNER JOIN ProvinceCluster AS b ON b.IsActive=1 AND b.ProvinceClusterID=a.ClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND a.ProvinceID=c.ProvinceID
	WHERE a.ProvinceActive=1 AND a.PreContractAdjust IS NOT NULL AND (a.SignActual BETWEEN @startTime AND @endTime))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
			,c.ProvinceEName AS ProvinceEName
			,NULL AS PreContractID
			,NULL AS OrieonPrice
			,NULL AS CEWME
			,NULL AS OrionME
			,NULL AS CFIPrice
			,NULL AS SparePrice
			,h.AdjustDisplayName AS AdjustDisplayName
	 FROM dbo.ProvinceCluster AS a
	 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ClusterID=a.ProvinceClusterID
	 CROSS JOIN #tmpTable AS h
	WHERE a.IsActive=1 --AND (NOT EXISTS (SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b WHERE (a.ProvinceClusterID = b.ClusterID) AND (b.PreContractAdjust IS NOT NULL)))
	)
	DROP TABLE #tmpTable
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport1_1_2]    Script Date: 06/16/2011 08:44:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_1_2', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_1_2
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-07
-- Description:	This is for the weekly report 1.1.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport1_1_2]
	@startTime DATE
   ,@endTime DATE
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS DECIMAL(18,6)
	SET @exchangeRate=1000000*dbo.FuncGetExchangeRate()
	SELECT x.ClusterEName AS ClusterEName
		  ,y.ProvinceEName AS ProvinceEName
		  ,w.CEWME AS CEWMESum
		  ,w.CEWMECount AS CEWMECount
		  ,u.TransferMoney AS TransferMoneySum
		  ,u.TransferCount AS TransferCount
		  ,v.CancelCount AS CancelCount FROM dbo.ProvinceCluster AS x
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS y ON y.Active=1 AND x.ProvinceClusterID=y.ClusterID
		
	LEFT OUTER JOIN (SELECT b.ClusterEName AS ClusterEName
		   ,c.ProvinceEName AS ProvinceEName
		   ,COUNT(distinct a.PreContractID) AS CEWMECount
		   ,SUM(a.CEWME) AS CEWME
		FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND (a.SignActual BETWEEN @startTime AND @endTime) AND a.ProvinceActive=1
		GROUP BY b.ClusterEName, c.ProvinceEName) AS w ON w.ClusterEName=x.ClusterEName AND w.ProvinceEName=y.ProvinceEName
		
	LEFT OUTER JOIN (SELECT c.ClusterEName AS ClusterEName
	       ,d.ProvinceEName AS ProvinceEName
	       ,COUNT(distinct a.PreContractID) AS TransferCount
	       ,SUM(a.TransferMoney)/@exchangeRate AS TransferMoney
	       FROM dbo.ContractTransfer AS a
		INNER JOIN dbo.vw_PreContractInfo AS b ON a.PreContractID=a.PreContractID
		INNER JOIN dbo.ProvinceCluster AS c ON c.IsActive=1 AND c.ProvinceClusterID=b.ClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS d ON d.Active=1 AND d.ProvinceID=b.ProvinceID
		WHERE a.TransferDate BETWEEN @startTime AND @endTime
		GROUP BY c.ClusterEName, d.ProvinceEName) AS u ON u.ClusterEName=x.ClusterEName AND u.ProvinceEName=y.ProvinceEName
	
	LEFT OUTER JOIN (SELECT b.ClusterEName AS ClusterEName
	       ,c.ProvinceEName AS ProvinceEName
	       ,COUNT(distinct a.PreContractID) AS CancelCount
	       FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.SignActual BETWEEN @startTime AND @endTime
		GROUP BY b.ClusterEName, c.ProvinceEName) AS v ON v.ClusterEName=x.ClusterEName AND v.ProvinceEName=y.ProvinceEName
	
	WHERE x.IsActive=1
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_3_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_3_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-17
-- Description:	This is for weekly report 1.3.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport1_3_1
   @employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	(SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	    FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.Delivered=1 AND a.AdjustID=21 AND a.StatusID IN (10,11))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	    FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
	 WHERE a.IsActive=1)
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport1_3_1_1]    Script Date: 06/20/2011 13:34:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_3_1_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_3_1_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-17
-- Description:	This is for weekly report 1.3.1.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport1_3_1_1] 
	@quarter INT
   ,@employeeID INT
   ,@catID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @afterQ NVARCHAR(15)
	DECLARE @noplan NVARCHAR(15)
	SET @afterQ=N'After Q' + CONVERT(NVARCHAR(1), @quarter)
	SET @noplan=N'No plan'
	
	DECLARE @startMonth INT
	SET @startMonth=(@quarter-1)*3+1;
	DECLARE @endMonth INT
	SET @endMonth=@startMonth+3
	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	CREATE TABLE #tmpMonthsTable (MonthNumber INT, MonthAbbr NVARCHAR(8) COLLATE database_default)
	INSERT INTO #tmpMonthsTable(MonthNumber,MonthAbbr) VALUES 
	 (@startMonth,@firstMonth)
	,(@startMonth+1,@secondMonth)
	,(@startMonth+2,@thirdMonth)
	,(NULL,@afterQ)
	,(NULL,@noplan)
	
	-- Delivery Status->Forecast to go
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.DeliveryPlan AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,1 AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		INNER JOIN #tmpMonthsTable AS d ON d.MonthAbbr=a.DeliveryPlan
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND a.CATID=@catID)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	      ,h.MonthAbbr AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,1 AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		CROSS JOIN #tmpMonthsTable AS h
		WHERE a.IsActive=1))
	-- Delivery Status->100% delivered
	UNION
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,NULL AS DeliveryPlan
	      ,1 AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND (a.Delivered=1) AND a.CATID=@catID)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS DeliveryPlan
	      ,1 AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		WHERE a.IsActive=1))
	-- Conversion forecast->(Within current Q/Beyond currentQ)->Total
	UNION
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,d.MonthAbbr AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,CASE WHEN ((MONTH(a.SignDue) BETWEEN @startMonth AND @endMonth)) THEN 1
	            WHEN (MONTH(a.SignDue)>@endMonth) THEN 0 END AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		INNER JOIN #tmpMonthsTable AS d ON (d.MonthNumber IS NOT NULL) AND d.MonthNumber=MONTH(a.SignDue)
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND (MONTH(a.SignDue)>=@startMonth) AND a.CATID=@catID)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,c.MonthAbbr AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,2 AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		CROSS JOIN #tmpMonthsTable AS c
		WHERE a.IsActive=1 AND (c.MonthNumber IS NOT NULL)))
	-- Conversion forecast->(Within current Q/Beyond current Q)->100% delivered
	UNION
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,NULL AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,CASE WHEN ((MONTH(a.SignDue) BETWEEN @startMonth AND @endMonth)) THEN 1 ELSE 0 END AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND a.Delivered=1 AND a.CATID=@catID)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,2 AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		WHERE a.IsActive=1))
	-- Conversion forecast->(Within cureent Q/Beyond current Q)->To be delivered in Q
	UNION
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,NULL AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,CASE WHEN ((MONTH(a.SignDue) BETWEEN @startMonth AND @endMonth)) THEN 1 ELSE 0 END AS CFWQToBeDeliveredInQ
	      FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth)
		      AND a.CATID=@catID)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,2 AS CFWQToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		WHERE a.IsActive=1))
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_3_2', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_3_2
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-17
-- Description:	This is for weekly report 1.3.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport1_3_2 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @afterQ NVARCHAR(15)
	DECLARE @noplan NVARCHAR(15)
	SET @afterQ=N'After Q' + CONVERT(NVARCHAR(1), @quarter)
	SET @noplan=N'No plan'
	
	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END
	CREATE TABLE #tmpTable(DeliveryPlan NVARCHAR(10) COLLATE database_default)
	INSERT INTO #tmpTable(DeliveryPlan)
	VALUES (@firstMonth),(@secondMonth),(@thirdMonth),(@afterQ),(@noplan)
	
	(SELECT b.ClusterEName AS ClusterEName
	       ,c.ProvinceEName AS ProvinceEName
	       ,a.DeliveryPlan AS DeliveryPlan
	       ,a.OrionME AS OrionME
	       ,a.CEWME AS CEWME
	       ,NULL AS Delivered
	    FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND b.ProvinceClusterID=a.ClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.TypeID IN (10,11)
	      AND a.DeliveryPlan IN (@firstMonth, @secondMonth, @afterQ, @noplan))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,c.DeliveryPlan AS DeliveryPlan
	       ,NULL AS OrionME 
	       ,NULL AS CEWME
	       ,NULL AS Delivered
	     FROM dbo.ProvinceCluster AS a
		 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND a.ProvinceClusterID=b.ClusterID
		 CROSS JOIN #tmpTable AS c
		 WHERE a.IsActive=1)
	UNION
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,NULL AS DeliveryPlan
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,1 AS Delivered
	    FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.Delivered=1 AND a.AdjustID=21 AND a.StatusID IN (10,11))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS DeliveryPlan
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,1 AS Delivered
	    FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
	 WHERE a.IsActive=1))
	
	DROP TABLE #tmpTable
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport3_2_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport3_2_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-20
-- Description:	This is for weekly report 3.2.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport3_2_1 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @endMonth INT
	SET @endMonth=(@quarter-1)*3
	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	(SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,d.BGCode AS BGCode
	      ,e.BDCode AS BDCode
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.CFIPrice AS CFIPrice
	      ,1 AS DeliveredOrToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND b.ProvinceClusterID=a.ClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
	INNER JOIN dbo.GetAssignedBG(@employeeID) AS d ON d.Active=1 AND d.BGID=a.BGID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.Active=1 AND e.BDID=a.BDID
	WHERE a.Delivered=1 AND MONTH(a.SignDue)>@endMonth)
	UNION
	(SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,d.BGCode AS BGCode
	      ,e.BDCode AS BDCode
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.CFIPrice AS CFIPrice
	      ,0 AS DeliveredOrToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND b.ProvinceClusterID=a.ClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
	INNER JOIN dbo.GetAssignedBG(@employeeID) AS d ON d.Active=1 AND d.BGID=a.BGID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.Active=1 AND e.BDID=a.BDID	
	WHERE a.Delivered=1 AND (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth)))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,f.BGCode AS BGCode
	       ,f.BDCode AS BDCode
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS CFIPrice
	       ,2 AS DeliveredOrToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
	 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
	 CROSS JOIN (SELECT d.BGCode AS BGCode
	                   ,e.BDCode AS BDCode FROM dbo.GetAssignedBG(@employeeID) AS d
					INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.Active=1 AND e.BGID=d.BGID
					WHERE d.Active=1) AS f
	 WHERE a.IsActive=1)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_3_3', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_3_3
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-17
-- Description:	This is for weekly report 1.3.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport1_3_3 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @afterQ NVARCHAR(15)
	DECLARE @noplan NVARCHAR(15)
	SET @afterQ=N'After Q' + CONVERT(NVARCHAR(1), @quarter)
	SET @noplan=N'No plan'
	
	DECLARE @startMonth INT
	SET @startMonth=(@quarter-1)*3+1;
	DECLARE @endMonth INT
	SET @endMonth=@startMonth+3
	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	CREATE TABLE #tmpMonthsTable (MonthNumber INT, MonthAbbr NVARCHAR(8) COLLATE database_default)
	INSERT INTO #tmpMonthsTable(MonthNumber,MonthAbbr) VALUES 
	 (@startMonth,@firstMonth)
	,(@startMonth+1,@secondMonth)
	,(@startMonth+2,@thirdMonth)
	,(NULL,@afterQ)
	,(NULL,@noplan)
	
	-- Delivery Status->Forecast to go
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.DeliveryPlan AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,1 AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		INNER JOIN #tmpMonthsTable AS d ON d.MonthAbbr=a.DeliveryPlan
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	      ,h.MonthAbbr AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,1 AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		CROSS JOIN #tmpMonthsTable AS h
		WHERE a.IsActive=1))
	-- Delivery Status->100% delivered
	UNION
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,NULL AS DeliveryPlan
	      ,1 AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND (a.Delivered=1))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS DeliveryPlan
	      ,1 AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		WHERE a.IsActive=1))
	-- Conversion forecast->(Within current Q/Beyond currentQ)->Total
	UNION
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,d.MonthAbbr AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,CASE WHEN ((MONTH(a.SignDue) BETWEEN @startMonth AND @endMonth)) THEN 1
	            WHEN (MONTH(a.SignDue)>@endMonth) THEN 0 END AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		INNER JOIN #tmpMonthsTable AS d ON (d.MonthNumber IS NOT NULL) AND d.MonthNumber=MONTH(a.SignDue)
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND (MONTH(a.SignDue)>=@startMonth))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,c.MonthAbbr AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,2 AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		CROSS JOIN #tmpMonthsTable AS c
		WHERE a.IsActive=1 AND (c.MonthNumber IS NOT NULL)))
	-- Conversion forecast->(Within current Q/Beyond current Q)->100% delivered
	UNION
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,NULL AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,CASE WHEN ((MONTH(a.SignDue) BETWEEN @startMonth AND @endMonth)) THEN 1 ELSE 0 END AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND a.Delivered=1)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,2 AS CFWQDelivered
	      ,NULL AS CFWQToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		WHERE a.IsActive=1))
	-- Conversion forecast->(Within cureent Q/Beyond current Q)->To be delivered in Q
	UNION
	((SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,NULL AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,CASE WHEN ((MONTH(a.SignDue) BETWEEN @startMonth AND @endMonth)) THEN 1 ELSE 0 END AS CFWQToBeDeliveredInQ
	      FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS DeliveryPlan
	      ,NULL AS DSDelivered
	      ,NULL AS DSForecastToGo
	      ,NULL AS CFWithinCurrentQ
	      ,NULL AS CFWQDelivered
	      ,2 AS CFWQToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		WHERE a.IsActive=1))
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_3_4', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_3_4
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-17
-- Description:	This is for weekly report 1.3.
-- =============================================
CREATE PROCEDURE WeeklyReport1_3_4 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;	
	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	
	DECLARE @startMonth INT
	SET @startMonth=(@quarter-1)*3+1;
	
	(SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.Delivered AS Delivered FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND (Month(a.SignDue) BETWEEN @startMonth AND (@startMonth+3)) 
		      AND a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS Delivered FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		WHERE a.IsActive=1)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_3_5', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_3_5
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-17
-- Description:	This is weekly report for 1.3.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport1_3_5 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	SET NOCOUNT ON;	
	
	DECLARE @startMonth INT
	SET @startMonth=(@quarter-1)*3+1;
	
	(SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.Delivered AS Delivered FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND (Month(a.SignDue)>(@startMonth+3)))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS Delivered FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		WHERE a.IsActive=1)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_3_6', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_3_6
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-17
-- Description:	This is for weekly report 1.3.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport1_3_6 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	
	DECLARE @startMonth INT
	SET @startMonth=(@quarter-1)*3+1;
	
	(SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.Delivered AS Delivered FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND (Month(a.SignDue)>(@startMonth+3)) 
		      AND a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS Delivered FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		WHERE a.IsActive=1)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_3_7', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_3_7
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-17
-- Description:	This is for weekly report 1.3.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport1_3_7
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;

    
	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	
	DECLARE @startMonth INT
	SET @startMonth=(@quarter-1)*3+1;
	
	(SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.Delivered AS Delivered FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND a.ClusterID=b.ProvinceClusterID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
		WHERE a.AdjustID=21 AND a.StatusID IN (10,11) AND (Month(a.SignDue)>(@startMonth+3)) 
		      AND a.Delivered=1)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS Delivered FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
		WHERE a.IsActive=1)
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport2_2]    Script Date: 06/16/2011 11:50:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport2_2_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport2_2_1
GO
CREATE PROCEDURE [dbo].[WeeklyReport2_2_1] 
   @employeeID INT
AS
BEGIN

	CREATE TABLE #tmpTable(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ProvinceID int, CATID int, CEWMETotal decimal(38,17))
	INSERT INTO #tmpTable(ProvinceID, CATID, CEWMETotal)
		SELECT TOP 10 b.ProvinceID, b.CATID, b.CEWMETotal FROM 
			((SELECT a.ProvinceID AS ProvinceID
			        ,a.CATID AS CATID
			        ,SUM(a.CEWME)AS CEWMETotal
				FROM dbo.vw_precontractinfo AS a
				INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND i.Active=1 AND i.ProvinceID=a.ProvinceID
				INNER JOIN dbo.GetAssignedCAT(@employeeID) AS j ON j.Active=1 AND j.CATID=a.CATID
				INNER JOIN dbo.GetAssignedBD(@employeeID) AS h ON h.Active=1 AND a.BDID=h.BDID
				WHERE a.Delivered=1
				GROUP BY a.provinceid, a.catid)
			  UNION
			  (SELECT c.ProvinceID AS ProvinceID
			         ,d.CATID AS CATID
			         ,NULL AS CEWMETotal FROM dbo.GetAssignedProvince(@employeeID) AS c
			   CROSS JOIN dbo.GetAssignedCAT(@employeeID) AS d)) AS b
			ORDER BY b.CEWMETotal DESC
	
	CREATE TABLE #tmpTable1(ProvinceID int, CATID int, BDID INT, CEWMESum decimal(38,17))
	INSERT INTO #tmpTable1(ProvinceID, CATID, BDID, CEWMESum)
	(SELECT c.ProvinceID, c.CATID, d.BDID, SUM(d.CEWME) AS CEWMESum FROM #tmpTable AS c
		LEFT OUTER JOIN dbo.vw_PreContractInfo AS d ON c.ProvinceID=d.ProvinceID AND c.CATID=d.CATID AND d.Delivered=1
		GROUP BY c.ProvinceID, c.CATID, d.BDID)
	
	SELECT v.ProvinceEName AS ProvinceEName
	      ,w.CATCode AS CATCode
	      ,x.BDCode AS BDCode
	      ,u.CEWMESum AS CEWMESum
	      ,y.ID FROM #tmpTable1 AS u
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS v ON u.ProvinceID=v.ProvinceID
	INNER JOIN dbo.GetAssignedCAT(@employeeID) AS w ON u.CATID=w.CATID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS x ON u.BDID=x.BDID
	INNER JOIN #tmpTable AS y ON u.ProvinceID=y.ProvinceID AND u.CATID=y.CATID
	ORDER BY y.ID ASC

	DROP TABLE #tmpTable
	DROP TABLE #tmpTable1
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport2_2_2]    Script Date: 06/20/2011 16:24:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport2_2_2', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport2_2_2
GO
CREATE PROCEDURE [dbo].[WeeklyReport2_2_2] 
    @quarter INT
   ,@employeeID INT
AS
BEGIN

	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
		
	CREATE TABLE #tmpTable(ID INT IDENTITY(1,1) PRIMARY KEY, ProvinceID int, CATID int, CEWMETotal decimal(38,17))
	INSERT INTO #tmpTable(ProvinceID, CATID, CEWMETotal)
		SELECT TOP 10 b.ProvinceID, b.CATID, b.CEWMETotal FROM 
			((SELECT a.ProvinceID AS ProvinceID
			        ,a.CATID AS CATID
			        ,SUM(a.CEWME)AS CEWMETotal
				FROM dbo.vw_precontractinfo AS a
				INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND i.Active=1 AND i.ProvinceID=a.ProvinceID
				INNER JOIN dbo.GetAssignedCAT(@employeeID) AS j ON j.Active=1 AND j.CATID=a.CATID
				INNER JOIN dbo.GetAssignedBD(@employeeID) AS h ON h.Active=1 AND a.BDID=h.BDID
				WHERE a.Delivered=1
				GROUP BY a.provinceid, a.catid)
			  UNION
			  (SELECT c.ProvinceID AS ProvinceID
			         ,d.CATID AS CATID
			         ,NULL AS CEWMETotal FROM dbo.GetAssignedProvince(@employeeID) AS c
			   CROSS JOIN dbo.GetAssignedCAT(@employeeID) AS d)) AS b
			ORDER BY b.CEWMETotal DESC
	
	CREATE TABLE #tmpTable1(ProvinceID int, CATID int, BDID INT, CEWMESum decimal(38,17))
	INSERT INTO #tmpTable1(ProvinceID, CATID, BDID, CEWMESum)
	(SELECT c.ProvinceID, c.CATID, d.BDID, SUM(d.CEWME) AS CEWMESum FROM #tmpTable AS c
		LEFT OUTER JOIN dbo.vw_PreContractInfo AS d ON c.ProvinceID=d.ProvinceID 
			AND c.CATID=d.CATID AND d.Delivered=1 AND (d.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth)) 
		GROUP BY c.ProvinceID, c.CATID, d.BDID)
	
	SELECT v.ProvinceEName AS ProvinceEName
	      ,w.CATCode AS CATCode
	      ,x.BDCode AS BDCode
	      ,u.CEWMESum AS CEWMESum
	      ,y.ID FROM #tmpTable1 AS u
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS v ON u.ProvinceID=v.ProvinceID
	INNER JOIN dbo.GetAssignedCAT(@employeeID) AS w ON u.CATID=w.CATID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS x ON u.BDID=x.BDID
	INNER JOIN #tmpTable AS y ON u.ProvinceID=y.ProvinceID AND u.CATID=y.CATID
	ORDER BY y.ID ASC	

	DROP TABLE #tmpTable
	DROP TABLE #tmpTable1
END
GO


/****** Object:  StoredProcedure [dbo].[WeeklyReport2_2_3]    Script Date: 06/20/2011 16:34:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport2_2_3', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport2_2_3
GO
CREATE PROCEDURE [dbo].[WeeklyReport2_2_3] 
    @quarter INT
   ,@employeeID INT
AS
BEGIN

	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
		
	CREATE TABLE #tmpTable(ID INT IDENTITY(1,1) PRIMARY KEY, ProvinceID int, CATID int, CEWMETotal decimal(38,17))
	INSERT INTO #tmpTable(ProvinceID, CATID, CEWMETotal)
		SELECT TOP 10 b.ProvinceID, b.CATID, b.CEWMETotal FROM 
			((SELECT a.ProvinceID AS ProvinceID
			        ,a.CATID AS CATID
			        ,SUM(a.CEWME)AS CEWMETotal
				FROM dbo.vw_precontractinfo AS a
				INNER JOIN dbo.GetAssignedProvince(@employeeID) AS i ON i.Active=1 AND i.Active=1 AND i.ProvinceID=a.ProvinceID
				INNER JOIN dbo.GetAssignedCAT(@employeeID) AS j ON j.Active=1 AND j.CATID=a.CATID
				INNER JOIN dbo.GetAssignedBD(@employeeID) AS h ON h.Active=1 AND a.BDID=h.BDID
				WHERE a.Delivered=1
				GROUP BY a.provinceid, a.catid)
			  UNION
			  (SELECT c.ProvinceID AS ProvinceID
			         ,d.CATID AS CATID
			         ,NULL AS CEWMETotal FROM dbo.GetAssignedProvince(@employeeID) AS c
			   CROSS JOIN dbo.GetAssignedCAT(@employeeID) AS d)) AS b
			ORDER BY b.CEWMETotal DESC
	
	CREATE TABLE #tmpTable1(ProvinceID int, CATID int, BDID INT, CEWMESum decimal(38,17))
	INSERT INTO #tmpTable1(ProvinceID, CATID, BDID, CEWMESum)
	(SELECT c.ProvinceID, c.CATID, d.BDID, SUM(d.CEWME) AS CEWMESum FROM #tmpTable AS c
		LEFT OUTER JOIN dbo.vw_PreContractInfo AS d ON c.ProvinceID=d.ProvinceID 
			AND c.CATID=d.CATID AND d.Delivered=1 AND (d.DeliveryPlan NOT IN (@firstMonth,@secondMonth,@thirdMonth)) 
		GROUP BY c.ProvinceID, c.CATID, d.BDID)
	
	SELECT v.ProvinceEName AS ProvinceEName
	      ,w.CATCode AS CATCode
	      ,x.BDCode AS BDCode
	      ,u.CEWMESum AS CEWMESum
	      ,y.ID FROM #tmpTable1 AS u
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS v ON u.ProvinceID=v.ProvinceID
	INNER JOIN dbo.GetAssignedCAT(@employeeID) AS w ON u.CATID=w.CATID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS x ON u.BDID=x.BDID
	INNER JOIN #tmpTable AS y ON u.ProvinceID=y.ProvinceID AND u.CATID=y.CATID
	ORDER BY y.ID ASC

	DROP TABLE #tmpTable
	DROP TABLE #tmpTable1
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport3_2_1_1]    Script Date: 06/20/2011 15:39:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport3_2_1_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport3_2_1_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-20
-- Description:	This is for weekly report 3.2.1.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport3_2_1_1] 
	@quarter INT
   ,@employeeID INT
   ,@catID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @endMonth INT
	SET @endMonth=(@quarter-1)*3
	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	(SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,d.BGCode AS BGCode
	      ,e.BDCode AS BDCode
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.CFIPrice AS CFIPrice
	      ,1 AS DeliveredOrToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND b.ProvinceClusterID=a.ClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
	INNER JOIN dbo.GetAssignedBG(@employeeID) AS d ON d.Active=1 AND d.BGID=a.BGID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.Active=1 AND e.BDID=a.BDID
	WHERE a.Delivered=1 AND MONTH(a.SignDue)>@endMonth AND a.CATID=@catID)
	UNION
	(SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,d.BGCode AS BGCode
	      ,e.BDCode AS BDCode
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.CFIPrice AS CFIPrice
	      ,0 AS DeliveredOrToBeDeliveredInQ FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND b.ProvinceClusterID=a.ClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
	INNER JOIN dbo.GetAssignedBG(@employeeID) AS d ON d.Active=1 AND d.BGID=a.BGID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.Active=1 AND e.BDID=a.BDID	
	WHERE a.Delivered=1 AND (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth)) AND a.CATID=@catID)
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,f.BGCode AS BGCode
	       ,f.BDCode AS BDCode
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS CFIPrice
	       ,2 AS DeliveredOrToBeDeliveredInQ FROM dbo.ProvinceCluster AS a
	 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
	 CROSS JOIN (SELECT d.BGCode AS BGCode
	                   ,e.BDCode AS BDCode FROM dbo.GetAssignedBG(@employeeID) AS d
					INNER JOIN dbo.GetAssignedBD(@employeeID) AS e ON e.Active=1 AND e.BGID=d.BGID
					WHERE d.Active=1) AS f
	 WHERE a.IsActive=1)
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport3_2_2]    Script Date: 06/20/2011 15:04:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport3_2_2', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport3_2_2
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-20
-- Description:	This is for weekly report 3.2.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport3_2_2] 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @firstMonth NVARCHAR(15)
	DECLARE @secondMonth NVARCHAR(15)
	DECLARE @thirdMonth NVARCHAR(15)
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
		
	(SELECT b.ClusterEName AS ClusterEName
	      ,c.ProvinceEName AS ProvinceEName
	      ,a.OrionME AS OrionME
	      ,a.CEWME AS CEWME
	      ,a.CFIPrice AS CFIPrice FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND b.ProvinceClusterID=a.ClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS c ON c.Active=1 AND c.ProvinceID=a.ProvinceID
	WHERE a.Delivered=1 AND (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth)))
	UNION
	(SELECT a.ClusterEName AS ClusterEName
	       ,b.ProvinceEName AS ProvinceEName
	       ,NULL AS OrionME
	       ,NULL AS CEWME
	       ,NULL AS CFIPrice FROM dbo.ProvinceCluster AS a
	 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND b.ClusterID=a.ProvinceClusterID
	 WHERE a.IsActive=1)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_5_1_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_5_1_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-21
-- Description:	This is for weekly report 1.5.1.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport1_5_1_1 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startMonth INT
	DECLARE @endMonth INT
	DECLARE @tmpQuarter INT
	DECLARE @exchangeRate DECIMAL(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1.17*1000000*@exchangeRate
	SET @tmpQuarter=1
	CREATE TABLE #tmpTable(CATID INT, TransferMoneySum DECIMAL(38,17), QuarterNo INT)
	WHILE @tmpQuarter<=@quarter
	BEGIN
		SET @startMonth=(@tmpQuarter-1)*3+1
		SET @endMonth=@startMonth+3
		INSERT INTO #tmpTable(CATID,TransferMoneySum,QuarterNo)
		(SELECT a.CATID,SUM(d.TransferMoney),@tmpQuarter FROM dbo.GetAssignedCAT(@employeeID) AS a
		 LEFT OUTER JOIN 
			(SELECT c.CATID,b.TransferMoney FROM dbo.ContractTransfer AS b
				INNER JOIN dbo.vw_PreContractInfo AS c ON b.PreContractID=c.PreContractID
				WHERE MONTH(b.TransferDate) BETWEEN @startMonth AND @endMonth) AS d
		 ON d.CATID=a.CATID
		 WHERE a.Active=1
		 GROUP BY a.CATID)
		SET @tmpQuarter=@tmpQuarter+1
	END
	
	SELECT b.CATCode,a.TransferMoneySum/@exchangeRate AS TransferMoneySum,a.QuarterNo FROM #tmpTable AS a
	INNER JOIN dbo.CAT AS b ON a.CATID=b.CATID
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_5_1_2', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_5_1_2
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-21
-- Description:	This is for weekly report 1.5.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport1_5_1_2 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startMonth INT
	DECLARE @endMonth INT
	DECLARE @firstMonth NVARCHAR(5)
	DECLARE @secondMonth NVARCHAR(5)
	DECLARE @thirdMonth NVARCHAR(5)
	DECLARE @afterQ NVARCHAR(15)	
	DECLARE @toBeCancelled NVARCHAR(20)
	DECLARE @testLease NVARCHAR(15)
	DECLARE @tryBuy NVARCHAR(15)
	DECLARE @upside NVARCHAR(10)
	DECLARE @outlook NVARCHAR(10)
	DECLARE @risk NVARCHAR(5)
	SET @startMonth=(@quarter-1)*3+1
	SET @endMonth=@startMonth+3
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	SET @afterQ=N'After Q' + CONVERT(NVARCHAR(1), @quarter)	
	SET @upside=N'upside'
	SET @tryBuy=N'Try&buy'
	SET @testLease=N'Test&lease'
	SET @toBeCancelled=N'to be cancelled'
	SET @outlook=N'Outlook'
	SET @risk=N'Risk'
	CREATE TABLE #tmpTable(CATID INT
	                      ,OrderPath NVARCHAR(20) COLLATE database_default
	                      ,OrionMESum DECIMAL(38,17)
	                      ,IsImportant BIT)
	CREATE TABLE #tmpTable1(CATID INT
	                      ,OrderPath NVARCHAR(20) COLLATE database_default
	                      ,IsRisk BIT	                      
	                      ,OrionME DECIMAL(38,17))
	CREATE TABLE #tmpTable2(OrderPath NVARCHAR(20) COLLATE database_default, IsImportant BIT, OrderSeq INT)
	INSERT INTO #tmpTable2(OrderPath, IsImportant,OrderSeq)
	VALUES (@firstMonth,1,1),(@secondMonth,1,2),(@thirdMonth,1,3),(@upside,1,6)
	       ,(@afterQ,0,8),(@toBeCancelled,0,9),(@tryBuy,0,10),(@testLease,0,11)
	
	INSERT INTO #tmpTable1(CATID,OrderPath,IsRisk,OrionME)
	((SELECT b.CATID
	      ,CASE WHEN a.AdjustID IN (18,20) THEN @testLease
	            WHEN a.AdjustID=19 THEN @tryBuy
	            WHEN c.CancelProcess IS NOT NULL THEN @toBeCancelled
	            WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(c.OperationDate) BETWEEN @startMonth AND @endMonth)
					THEN CONVERT(NVARCHAR(2),MONTH(c.OperationDate))
	            WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(c.OperationDate)>@endMonth)
					THEN a.DeliveryPlan
				WHEN (a.DeliveryPlan=@afterQ) 
				      AND (MONTH(c.OperationDate) BETWEEN @startMonth AND @endMonth)
					THEN @upside
				WHEN (a.DeliveryPlan=@afterQ) AND (MONTH(c.OperationDate)>@endMonth)
					THEN @afterQ
	            WHEN c.CancelProcess IS NULL THEN a.DeliveryPlan
	       END
	      ,CASE WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(c.OperationDate)>@endMonth)
					THEN 1
					ELSE 0 END
	      ,a.OrionME FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.GetAssignedCAT(@employeeID) AS b ON b.Active=1 AND b.CATID=a.CATID
	LEFT OUTER JOIN dbo.SalesFeedback AS c ON a.PreContractID=c.PreContractID)
	UNION
	(SELECT a.CATID,b.OrderPath,NULL,NULL FROM dbo.GetAssignedCAT(@employeeID) AS a
		CROSS JOIN #tmpTable2 AS b
		WHERE a.Active=1))
	
	INSERT INTO #tmpTable(CATID,OrderPath,OrionMESum,IsImportant)
	(SELECT a.CATID
	       ,a.OrderPath
	       ,SUM(a.OrionME)
	       ,CASE WHEN a.OrderPath NOT IN (@firstMonth,@secondMonth,@thirdMonth,@upside) THEN 0
	            ELSE 1
	            END AS IsImportant FROM #tmpTable1 AS a
	GROUP BY a.CATID,a.OrderPath)
	
	DECLARE @tmpCATID INT
	DECLARE @tmpOrionMESum DECIMAL(38,17)
	DECLARE tmpCursor INSENSITIVE CURSOR FOR
		(SELECT a.CATID,SUM(a.OrionMESum) FROM #tmpTable AS a 
			WHERE (a.OrderPath IN (@firstMonth,@secondMonth,@thirdMonth))
			GROUP BY a.CATID)
	FOR READ ONLY
	OPEN tmpCursor
	FETCH NEXT FROM tmpCursor INTO @tmpCATID,@tmpOrionMESum
	WHILE @@FETCH_STATUS=0
	BEGIN
		INSERT INTO #tmpTable(CATID,OrderPath,OrionMESum,IsImportant)
		VALUES(@tmpCATID,@outlook,@tmpOrionMESum,1)
		FETCH NEXT FROM tmpCursor INTO @tmpCATID,@tmpOrionMESum
	END
	CLOSE tmpCursor
	DEALLOCATE tmpCursor
	
	INSERT INTO #tmpTable(CATID,OrderPath,OrionMESum,IsImportant)
	(SELECT a.CATID,@risk,SUM(a.OrionME),1 FROM #tmpTable1 AS a
	 WHERE a.IsRisk=1 GROUP BY a.CATID)
	IF NOT EXISTS(SELECT TOP 1 a.CATID FROM #tmpTable AS a WHERE a.OrderPath=@risk)
	BEGIN
		INSERT INTO #tmpTable(CATID,OrderPath,OrionMESum,IsImportant)
		(SELECT a.CATID,@risk,NULL,1 FROM dbo.GetAssignedCAT(@employeeID) AS a WHERE a.Active=1)
	END
	
	INSERT INTO #tmpTable2(OrderPath,IsImportant,OrderSeq)
	VALUES (@outlook,1,4),(@risk,1,5)
	
	SELECT b.CATCode,a.OrderPath,a.OrionMESum,a.IsImportant,c.OrderSeq FROM #tmpTable AS a 
	INNER JOIN dbo.CAT AS b ON b.CATID=a.CATID
	INNER JOIN #tmpTable2 AS c ON c.OrderPath=a.OrderPath ORDER BY a.CATID ASC, c.OrderSeq
	
	DROP TABLE #tmpTable1
	DROP TABLE #tmpTable2
	DROP TABLE #tmpTable
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport1_5_1_3]    Script Date: 06/22/2011 12:12:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_5_1_3', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_5_1_3
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-21
-- Description:	This is for weekly report 1.5.1.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport1_5_1_3] 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startMonth INT
	DECLARE @endMonth INT
	DECLARE @tmpQuarter INT
	DECLARE @exchangeRate DECIMAL(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1.17*1000000*@exchangeRate
	SET @tmpQuarter=1
	CREATE TABLE #tmpTable(BGID INT, BDID INT, TransferMoneySum DECIMAL(38,17), QuarterNo INT)
	WHILE @tmpQuarter<=@quarter
	BEGIN
		SET @startMonth=(@tmpQuarter-1)*3+1
		SET @endMonth=@startMonth+3
		INSERT INTO #tmpTable(BGID,BDID,TransferMoneySum,QuarterNo)
		(SELECT a.BGID,h.BDID,SUM(d.TransferMoney),@tmpQuarter FROM dbo.GetAssignedBG(@employeeID) AS a
		 INNER JOIN dbo.GetAssignedBD(@employeeID) AS h ON h.BGID=a.BGID
		 LEFT OUTER JOIN 
			(SELECT c.BGID,c.BDID,b.TransferMoney FROM dbo.ContractTransfer AS b
				INNER JOIN dbo.vw_PreContractInfo AS c ON b.PreContractID=c.PreContractID
				WHERE MONTH(b.TransferDate) BETWEEN @startMonth AND @endMonth) AS d
		 ON d.BDID=h.BDID
		 GROUP BY a.BGID,h.BDID)
		SET @tmpQuarter=@tmpQuarter+1
	END
	
	SELECT b.BGCode,c.BDCode,a.TransferMoneySum/@exchangeRate AS TransferMoneySum,a.QuarterNo FROM #tmpTable AS a
	INNER JOIN dbo.BG AS b ON a.BGID=b.BGID
	INNER JOIN dbo.BD AS c ON a.BDID=c.BDID
END

GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport1_5_1_4]    Script Date: 06/22/2011 12:17:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_5_1_4', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_5_1_4
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-21
-- Description:	This is for weekly report 1.5.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport1_5_1_4] 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startMonth INT
	DECLARE @endMonth INT
	DECLARE @firstMonth NVARCHAR(5)
	DECLARE @secondMonth NVARCHAR(5)
	DECLARE @thirdMonth NVARCHAR(5)
	DECLARE @afterQ NVARCHAR(15)	
	DECLARE @toBeCancelled NVARCHAR(20)
	DECLARE @testLease NVARCHAR(15)
	DECLARE @tryBuy NVARCHAR(15)
	DECLARE @upside NVARCHAR(10)
	DECLARE @outlook NVARCHAR(10)
	DECLARE @risk NVARCHAR(5)
	SET @startMonth=(@quarter-1)*3+1
	SET @endMonth=@startMonth+3
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	SET @afterQ=N'After Q' + CONVERT(NVARCHAR(1), @quarter)	
	SET @upside=N'upside'
	SET @tryBuy=N'Try&buy'
	SET @testLease=N'Test&lease'
	SET @toBeCancelled=N'to be cancelled'
	SET @outlook=N'Outlook'
	SET @risk=N'Risk'
	CREATE TABLE #tmpTable(BGID INT
						  ,BDID INT
	                      ,OrderPath NVARCHAR(20) COLLATE database_default
	                      ,OrionMESum DECIMAL(38,17)
	                      ,IsImportant BIT)
	CREATE TABLE #tmpTable1(BGID INT
						   ,BDID INT
	                       ,OrderPath NVARCHAR(20) COLLATE database_default
	                       ,IsRisk BIT	                      
	                       ,OrionME DECIMAL(38,17))
	CREATE TABLE #tmpTable2(OrderPath NVARCHAR(20) COLLATE database_default, IsImportant BIT, OrderSeq INT)
	INSERT INTO #tmpTable2(OrderPath, IsImportant,OrderSeq)
	VALUES (@firstMonth,1,1),(@secondMonth,1,2),(@thirdMonth,1,3),(@upside,1,6)
	       ,(@afterQ,0,8),(@toBeCancelled,0,9),(@tryBuy,0,10),(@testLease,0,11)
	
	INSERT INTO #tmpTable1(BGID,BDID,OrderPath,IsRisk,OrionME)
	((SELECT b.BGID,h.BDID
	      ,CASE WHEN a.AdjustID IN (18,20) THEN @testLease
	            WHEN a.AdjustID=19 THEN @tryBuy
	            WHEN c.CancelProcess IS NOT NULL THEN @toBeCancelled
	            WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(c.OperationDate) BETWEEN @startMonth AND @endMonth)
					THEN CONVERT(NVARCHAR(2),MONTH(c.OperationDate))
	            WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(c.OperationDate)>@endMonth)
					THEN a.DeliveryPlan
				WHEN (a.DeliveryPlan=@afterQ) 
				      AND (MONTH(c.OperationDate) BETWEEN @startMonth AND @endMonth)
					THEN @upside
				WHEN (a.DeliveryPlan=@afterQ) AND (MONTH(c.OperationDate)>@endMonth)
					THEN @afterQ
	            WHEN c.CancelProcess IS NULL THEN a.DeliveryPlan
	       END
	      ,CASE WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(c.OperationDate)>@endMonth)
					THEN 1
					ELSE 0 END
	      ,a.OrionME FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.GetAssignedBG(@employeeID) AS b ON b.Active=1 AND b.BGID=a.BGID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS h ON h.Active=1 AND h.BDID=a.BDID
	LEFT OUTER JOIN dbo.SalesFeedback AS c ON a.PreContractID=c.PreContractID)
	UNION
	(SELECT a.BGID,h.BDID,b.OrderPath,NULL,NULL FROM dbo.GetAssignedBG(@employeeID) AS a
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS h ON h.Active=1 AND h.BGID=a.BGID
		CROSS JOIN #tmpTable2 AS b
		WHERE a.Active=1))
	
	INSERT INTO #tmpTable(BGID,BDID,OrderPath,OrionMESum,IsImportant)
	(SELECT a.BGID
		   ,a.BDID
	       ,a.OrderPath
	       ,SUM(a.OrionME)
	       ,CASE WHEN a.OrderPath NOT IN (@firstMonth,@secondMonth,@thirdMonth,@upside) THEN 0
	            ELSE 1
	            END AS IsImportant FROM #tmpTable1 AS a
	GROUP BY a.BGID,a.BDID,a.OrderPath)
	
	DECLARE @tmpBGID INT
	DECLARE @tmpBDID INT
	DECLARE @tmpOrionMESum DECIMAL(38,17)
	DECLARE tmpCursor INSENSITIVE CURSOR FOR
		(SELECT a.BGID,a.BDID,SUM(a.OrionMESum) FROM #tmpTable AS a 
			WHERE (a.OrderPath IN (@firstMonth,@secondMonth,@thirdMonth))
			GROUP BY a.BGID,a.BDID)
	FOR READ ONLY
	OPEN tmpCursor
	FETCH NEXT FROM tmpCursor INTO @tmpBGID,@tmpBDID,@tmpOrionMESum
	WHILE @@FETCH_STATUS=0
	BEGIN
		INSERT INTO #tmpTable(BGID,BDID,OrderPath,OrionMESum,IsImportant)
		VALUES(@tmpBGID,@tmpBDID,@outlook,@tmpOrionMESum,1)
		FETCH NEXT FROM tmpCursor INTO @tmpBGID,@tmpBDID,@tmpOrionMESum
	END
	CLOSE tmpCursor
	DEALLOCATE tmpCursor
	
	INSERT INTO #tmpTable(BGID,BDID,OrderPath,OrionMESum,IsImportant)
	(SELECT a.BGID,a.BDID,@risk,SUM(a.OrionME),1 FROM #tmpTable1 AS a
	 WHERE a.IsRisk=1 GROUP BY a.BGID,a.BDID)
	IF NOT EXISTS(SELECT TOP 1 a.BDID FROM #tmpTable AS a WHERE a.OrderPath=@risk)
	BEGIN
		INSERT INTO #tmpTable(BGID,BDID,OrderPath,OrionMESum,IsImportant)
		(SELECT a.BGID,b.BDID,@risk,NULL,1 FROM dbo.GetAssignedBG(@employeeID) AS a
		 INNER JOIN dbo.GetAssignedBD(@employeeID) AS b ON b.Active=1 AND a.BGID=b.BDID
		 WHERE a.Active=1)
	END
	
	INSERT INTO #tmpTable2(OrderPath,IsImportant,OrderSeq)
	VALUES (@outlook,1,4),(@risk,1,5)
	
	SELECT b.BGCode,h.BDCode,a.OrderPath,a.OrionMESum,a.IsImportant,c.OrderSeq FROM #tmpTable AS a 
	INNER JOIN dbo.BG AS b ON b.BGID=a.BGID
	INNER JOIN dbo.BD AS h ON h.BDID=a.BDID
	INNER JOIN #tmpTable2 AS c ON c.OrderPath=a.OrderPath ORDER BY a.BGID ASC,a.BDID ASC,c.OrderSeq ASC
	
	DROP TABLE #tmpTable1
	DROP TABLE #tmpTable2
	DROP TABLE #tmpTable
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport1_5_1_5]    Script Date: 06/22/2011 13:09:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_5_1_5', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_5_1_5
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-21
-- Description:	This is for weekly report 1.5.1.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport1_5_1_5] 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startMonth INT
	DECLARE @endMonth INT
	DECLARE @tmpQuarter INT
	DECLARE @exchangeRate DECIMAL(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1.17*1000000*@exchangeRate
	SET @tmpQuarter=1
	CREATE TABLE #tmpTable(ClusterID INT, ProvinceID INT, TransferMoneySum DECIMAL(38,17), QuarterNo INT)
	WHILE @tmpQuarter<=@quarter
	BEGIN
		SET @startMonth=(@tmpQuarter-1)*3+1
		SET @endMonth=@startMonth+3
		INSERT INTO #tmpTable(ClusterID,ProvinceID,TransferMoneySum,QuarterNo)
		(SELECT a.ProvinceClusterID,h.ProvinceID,SUM(d.TransferMoney),@tmpQuarter FROM dbo.ProvinceCluster AS a
		 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS h ON h.Active=1 AND h.ClusterID=a.ProvinceClusterID
		 LEFT OUTER JOIN 
			(SELECT c.ClusterID,c.ProvinceID,b.TransferMoney FROM dbo.ContractTransfer AS b
				INNER JOIN dbo.vw_PreContractInfo AS c ON b.PreContractID=c.PreContractID
				WHERE MONTH(b.TransferDate) BETWEEN @startMonth AND @endMonth) AS d
		 ON d.ProvinceID=h.ProvinceID
		 WHERE a.IsActive=1
		 GROUP BY a.ProvinceClusterID,h.ProvinceID)
		SET @tmpQuarter=@tmpQuarter+1
	END
	
	SELECT b.ClusterEName,h.ProvinceEName,a.TransferMoneySum/@exchangeRate AS TransferMoneySum,a.QuarterNo FROM #tmpTable AS a
	INNER JOIN dbo.ProvinceCluster AS b ON a.ClusterID=b.ProvinceClusterID
	INNER JOIN dbo.Province AS h ON h.ProvinceID=a.ProvinceID
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport1_5_1_6]    Script Date: 06/22/2011 13:14:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_5_1_6', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_5_1_6
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-21
-- Description:	This is for weekly report 1.5.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport1_5_1_6] 
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @startMonth INT
	DECLARE @endMonth INT
	DECLARE @firstMonth NVARCHAR(5)
	DECLARE @secondMonth NVARCHAR(5)
	DECLARE @thirdMonth NVARCHAR(5)
	DECLARE @afterQ NVARCHAR(15)	
	DECLARE @toBeCancelled NVARCHAR(20)
	DECLARE @testLease NVARCHAR(15)
	DECLARE @tryBuy NVARCHAR(15)
	DECLARE @upside NVARCHAR(10)
	DECLARE @outlook NVARCHAR(10)
	DECLARE @risk NVARCHAR(5)
	SET @startMonth=(@quarter-1)*3+1
	SET @endMonth=@startMonth+3
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	SET @afterQ=N'After Q' + CONVERT(NVARCHAR(1), @quarter)	
	SET @upside=N'upside'
	SET @tryBuy=N'Try&buy'
	SET @testLease=N'Test&lease'
	SET @toBeCancelled=N'to be cancelled'
	SET @outlook=N'Outlook'
	SET @risk=N'Risk'
	CREATE TABLE #tmpTable(ClusterID INT
						  ,ProvinceID INT
	                      ,OrderPath NVARCHAR(20) COLLATE database_default
	                      ,OrionMESum DECIMAL(38,17)
	                      ,IsImportant BIT)
	CREATE TABLE #tmpTable1(ClusterID INT
						   ,ProvinceID INT
	                       ,OrderPath NVARCHAR(20) COLLATE database_default
	                       ,IsRisk BIT	                      
	                       ,OrionME DECIMAL(38,17))
	CREATE TABLE #tmpTable2(OrderPath NVARCHAR(20) COLLATE database_default, IsImportant BIT, OrderSeq INT)
	INSERT INTO #tmpTable2(OrderPath, IsImportant,OrderSeq)
	VALUES (@firstMonth,1,1),(@secondMonth,1,2),(@thirdMonth,1,3),(@upside,1,6)
	       ,(@afterQ,0,8),(@toBeCancelled,0,9),(@tryBuy,0,10),(@testLease,0,11)
	
	INSERT INTO #tmpTable1(ClusterID,ProvinceID,OrderPath,IsRisk,OrionME)
	((SELECT b.ProvinceClusterID,h.ProvinceID
	      ,CASE WHEN a.AdjustID IN (18,20) THEN @testLease
	            WHEN a.AdjustID=19 THEN @tryBuy
	            WHEN c.CancelProcess IS NOT NULL THEN @toBeCancelled
	            WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(c.OperationDate) BETWEEN @startMonth AND @endMonth)
					THEN CONVERT(NVARCHAR(2),MONTH(c.OperationDate))
	            WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(c.OperationDate)>@endMonth)
					THEN a.DeliveryPlan
				WHEN (a.DeliveryPlan=@afterQ) 
				      AND (MONTH(c.OperationDate) BETWEEN @startMonth AND @endMonth)
					THEN @upside
				WHEN (a.DeliveryPlan=@afterQ) AND (MONTH(c.OperationDate)>@endMonth)
					THEN @afterQ
	            WHEN c.CancelProcess IS NULL THEN a.DeliveryPlan
	       END
	      ,CASE WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(c.OperationDate)>@endMonth)
					THEN 1
					ELSE 0 END
	      ,a.OrionME FROM dbo.vw_PreContractInfo AS a
	INNER JOIN dbo.ProvinceCluster AS b ON b.IsActive=1 AND b.ProvinceClusterID=a.ClusterID
	INNER JOIN dbo.GetAssignedProvince(@employeeID) AS h ON h.Active=1 AND h.ProvinceID=a.ProvinceID
	LEFT OUTER JOIN dbo.SalesFeedback AS c ON a.PreContractID=c.PreContractID)
	UNION
	(SELECT a.ProvinceClusterID,h.ProvinceID,b.OrderPath,NULL,NULL FROM dbo.ProvinceCluster AS a
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS h ON h.Active=1 AND h.ClusterID=a.ProvinceClusterID
		CROSS JOIN #tmpTable2 AS b
		WHERE a.IsActive=1))
	
	INSERT INTO #tmpTable(ClusterID,ProvinceID,OrderPath,OrionMESum,IsImportant)
	(SELECT a.ClusterID
		   ,a.ProvinceID
	       ,a.OrderPath
	       ,SUM(a.OrionME)
	       ,CASE WHEN a.OrderPath NOT IN (@firstMonth,@secondMonth,@thirdMonth,@upside) THEN 0
	            ELSE 1
	            END AS IsImportant FROM #tmpTable1 AS a
	GROUP BY a.ClusterID,a.ProvinceID,a.OrderPath)
	
	DECLARE @tmpClusterID INT
	DECLARE @tmpProvinceID INT
	DECLARE @tmpOrionMESum DECIMAL(38,17)
	DECLARE tmpCursor INSENSITIVE CURSOR FOR
		(SELECT a.ClusterID,a.ProvinceID,SUM(a.OrionMESum) FROM #tmpTable AS a 
			WHERE (a.OrderPath IN (@firstMonth,@secondMonth,@thirdMonth))
			GROUP BY a.ClusterID,a.ProvinceID)
	FOR READ ONLY
	OPEN tmpCursor
	FETCH NEXT FROM tmpCursor INTO @tmpClusterID,@tmpProvinceID,@tmpOrionMESum
	WHILE @@FETCH_STATUS=0
	BEGIN
		INSERT INTO #tmpTable(ClusterID,ProvinceID,OrderPath,OrionMESum,IsImportant)
		VALUES(@tmpClusterID,@tmpProvinceID,@outlook,@tmpOrionMESum,1)
		FETCH NEXT FROM tmpCursor INTO @tmpClusterID,@tmpProvinceID,@tmpOrionMESum
	END
	CLOSE tmpCursor
	DEALLOCATE tmpCursor
	
	INSERT INTO #tmpTable(ClusterID,ProvinceID,OrderPath,OrionMESum,IsImportant)
	(SELECT a.ClusterID,a.ProvinceID,@risk,SUM(a.OrionME),1 FROM #tmpTable1 AS a
	 WHERE a.IsRisk=1 GROUP BY a.ClusterID,a.ProvinceID)
	IF NOT EXISTS(SELECT TOP 1 a.ProvinceID FROM #tmpTable AS a WHERE a.OrderPath=@risk)
	BEGIN
		INSERT INTO #tmpTable(ClusterID,ProvinceID,OrderPath,OrionMESum,IsImportant)
		(SELECT a.ProvinceClusterID,b.ProvinceID,@risk,NULL,1 FROM dbo.ProvinceCluster AS a
		 INNER JOIN dbo.GetAssignedProvince(@employeeID) AS b ON b.Active=1 AND a.ProvinceClusterID=b.ClusterID
		 WHERE a.IsActive=1)
	END
	
	INSERT INTO #tmpTable2(OrderPath,IsImportant,OrderSeq)
	VALUES (@outlook,1,4),(@risk,1,5)
	
	SELECT b.ClusterEName,h.ProvinceEName,a.OrderPath,a.OrionMESum,a.IsImportant,c.OrderSeq FROM #tmpTable AS a 
	INNER JOIN dbo.ProvinceCluster AS b ON b.ProvinceClusterID=a.ClusterID
	INNER JOIN dbo.Province AS h ON h.ProvinceID=a.ProvinceID
	INNER JOIN #tmpTable2 AS c ON c.OrderPath=a.OrderPath ORDER BY a.ClusterID ASC,a.ProvinceID ASC,c.OrderSeq ASC
	
	DROP TABLE #tmpTable1
	DROP TABLE #tmpTable2
	DROP TABLE #tmpTable
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport4_1', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport4_1
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-22
-- Description:	This is for weekly report 4.
-- =============================================
CREATE PROCEDURE dbo.WeeklyReport4_1
	@quarter INT
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS DECIMAL(18,6)
	DECLARE @startMonth INT
	DECLARE @endMonth INT
	DECLARE @firstMonth NVARCHAR(5)
	DECLARE @secondMonth NVARCHAR(5)
	DECLARE @thirdMonth NVARCHAR(5)
	DECLARE @afterQ NVARCHAR(15)	
	DECLARE @toBeCancelled NVARCHAR(20)
	DECLARE @testLease NVARCHAR(15)
	DECLARE @tryBuy NVARCHAR(15)
	DECLARE @upside NVARCHAR(10)
	DECLARE @outlook NVARCHAR(10)
	DECLARE @risk NVARCHAR(5)
	SET @startMonth=(@quarter-1)*3+1
	SET @endMonth=@startMonth+3
	SET @firstMonth = 
		CASE @quarter WHEN 1 THEN N'Jan.'
		WHEN 2 THEN N'Apr.'
		WHEN 3 THEN N'Jul.'
		WHEN 4 THEN N'Oct.'
		END 
	SET @secondMonth = 
		CASE @quarter WHEN 1 THEN N'Feb.'
		WHEN 2 THEN N'May'
		WHEN 3 THEN N'Aug.'
		WHEN 4 THEN N'Nov.'
		END 
	SET @thirdMonth = 
		CASE @quarter WHEN 1 THEN N'Mar.'
		WHEN 2 THEN N'Jun.'
		WHEN 3 THEN N'Sept.'
		WHEN 4 THEN N'Dec.'
		END 
	SET @afterQ=N'After Q' + CONVERT(NVARCHAR(1), @quarter)	
	SET @upside=N'upside'
	SET @tryBuy=N'Try&buy'
	SET @testLease=N'Test&lease'
	SET @toBeCancelled=N'to be cancelled'
	SET @outlook=N'Outlook'
	SET @risk=N'Risk'
	SET @exchangeRate=1.17*1000000*dbo.FuncGetExchangeRate()
	
	SELECT c.DicItemValue AS PreContractType
	      ,d.DicItemValue AS PreContractCatalog
	      ,e.DicItemValue AS PreContractStatus
	      ,a.PreContractNo AS PreContractNo
	      ,a.BDCode
	      ,a.BGCode
	      ,f.DeadlineOfTrying
	      ,a.ContractName
	      ,a.ProductName
	      ,a.ClusterCName
	      ,a.ProvinceCName
	      ,g.ProvinceEName
	      ,a.CATCode
	      ,a.CEWPromised/@exchangeRate AS CEWPromisedME
	      ,a.CEWME
	      ,a.SignActual
	      ,a.OrionME
	      ,a.SignDue
	      ,a.TradeMonth
	      ,a.CFIPrice AS CFIME
	      ,a.ERPReceived
	      ,DATEDIFF(DAY, a.ERPReceived,GETDATE()) AS ExistingDays
	      ,a.SpecialApprove
	      ,DATEDIFF(MONTH, a.ERPReceived,GETDATE()) AS ExistingMonths
	      ,a.Delivered
	      ,a.DeliveryPercent
	      ,a.DeliveryDate
	      ,a.DeliveryPlan
	      ,DATEDIFF(MONTH, a.ERPReceived,GETDATE()) AS Aging
	      ,CASE WHEN a.AdjustID IN (18,20) THEN @testLease
	            WHEN a.AdjustID=19 THEN @tryBuy
	            WHEN h.CancelProcess IS NOT NULL THEN @toBeCancelled
	            WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(h.OperationDate) BETWEEN @startMonth AND @endMonth)
					THEN CONVERT(NVARCHAR(2),MONTH(h.OperationDate))
	            WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(h.OperationDate)>@endMonth)
					THEN a.DeliveryPlan
				WHEN (a.DeliveryPlan=@afterQ) 
				      AND (MONTH(h.OperationDate) BETWEEN @startMonth AND @endMonth)
					THEN @upside
				WHEN (a.DeliveryPlan=@afterQ) AND (MONTH(h.OperationDate)>@endMonth)
					THEN @afterQ
	            WHEN h.CancelProcess IS NULL THEN a.DeliveryPlan
	       END AS OrderPath
	      ,CASE WHEN (a.DeliveryPlan IN (@firstMonth,@secondMonth,@thirdMonth))
	                 AND (MONTH(h.OperationDate)>@endMonth)
					THEN 1
					ELSE 0 END AS IsRisk
	      ,n.DicItemValue AS SignType
	      ,i.DicItemValue AS TreatmentWay
	      ,NULL AS SignToCancelTryLeasePrecontract --cancel of try/lease precontract signment
	      ,j.DicItemValue AS CancelProcess
	      ,MONTH(h.OperationDate) AS ProcessingMonth
	      ,k.DicItemValue AS ProblemLV1
	      ,m.DicItemValue AS ProblemLV2
	      ,NULL AS ProjectCatalog--project catalog
	      ,NULL AS NeedSupportFromCompany --need company support
	      ,h.PeopleInCharge
	      ,h.Comments
	      FROM dbo.vw_PreContractInfo AS a
	    INNER JOIN dbo.PreContractInfo AS f ON f.PreContractID=a.PreContractID
		INNER JOIN dbo.GetAssignedProvince(@employeeID) AS g ON a.ProvinceID=g.ProvinceID
		INNER JOIN dbo.GetAssignedBG(@employeeID) AS u ON u.BGID=a.BGID
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS v ON v.BDID=a.BDID
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS w ON w.CATID=a.CATID
		INNER JOIN dbo.DicItem AS c ON a.TypeID=c.DicItemID
		INNER JOIN dbo.DicItem AS d ON a.AdjustID=d.DicItemID
		INNER JOIN dbo.DicItem AS e ON a.TypeID=e.DicItemID
		LEFT OUTER JOIN dbo.ContractTransfer AS b ON a.PreContractID=b.PreContractID
		LEFT OUTER JOIN dbo.SalesFeedback AS h ON h.PreContractID=a.PreContractID
		LEFT OUTER JOIN dbo.DicItem AS n ON n.DicItemID=h.SignType
		LEFT OUTER JOIN dbo.DicItem AS i ON i.DicItemID=h.TreatmentWay
		LEFT OUTER JOIN dbo.DicItem AS j ON j.DicItemID=h.CancelProcess
		LEFT OUTER JOIN dbo.DicItem AS k ON k.DicItemID=h.ProblemLV1
		LEFT OUTER JOIN dbo.DicItem AS m ON m.DicItemID=h.ProblemLV2
		LEFT OUTER JOIN dbo.DicItem AS p ON p.DicItemID=h.InvoiceStatus
		WHERE a.CATActive=1 AND a.BDActive=1 AND a.ProvinceActive=1
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.GetExchangeRate', 'P') IS NOT NULL
	DROP PROCEDURE dbo.GetExchangeRate
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-07
-- Description:	This is to return exchange rate.
-- =============================================
CREATE PROCEDURE dbo.GetExchangeRate
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT TOP 1 a.Rate FROM dbo.ExchangeRate AS a
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport1_1_3]    Script Date: 06/24/2011 13:38:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_1_3', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_1_3
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-15
-- Description:	This is for weekly report 1.1.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport1_1_3] 
	@startTime DATE
   ,@endTime DATE
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS decimal(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1.17*1000000*@exchangeRate
	CREATE TABLE #tmpTable(AdjustDisplayName NVARCHAR(15))
	INSERT INTO #tmpTable (AdjustDisplayName)
	VALUES ('Pre-'),('Test/Lease'),('Try&Buy')
	(SELECT
	  b.CATCode AS CATCode
	  ,a.PreContractID AS PreContractID
	  ,a.OrionPrice AS OrionPrice
	  ,a.CEWME AS CEWME
	  ,a.OrionME AS OrionME
	  ,a.CFIPrice AS CFIPrice
	  ,a.SparePrice/@exchangeRate AS SparePrice
	  ,CASE a.AdjustID WHEN 21 THEN 'Pre-'
		WHEN 18 THEN 'Test/Lease'
		WHEN 20 THEN 'Test/Lease'
		WHEN 19 THEN 'Try&Buy'
		ELSE 'Others' END AS AdjustDisplayName
	FROM
	  vw_PreContractInfo AS a
	INNER JOIN dbo.GetAssignedCAT(@employeeID) AS b ON b.Active=1 AND a.CATID=b.CATID
	WHERE a.PreContractAdjust IS NOT NULL AND (a.SignActual BETWEEN @startTime AND @endTime))
	UNION
	(SELECT a.CATCode AS CATCode
			,NULL AS PreContractID
			,NULL AS OrieonPrice
			,NULL AS CEWME
			,NULL AS OrionME
			,NULL AS CFIPrice
			,NULL AS SparePrice
			,h.AdjustDisplayName AS AdjustDisplayName
	 FROM dbo.GetAssignedCAT(@employeeID) AS a
	 CROSS JOIN #tmpTable AS h
	WHERE a.Active=1 --AND (NOT EXISTS (SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b WHERE (a.ProvinceClusterID = b.ClusterID) AND (b.PreContractAdjust IS NOT NULL)))
	)
	DROP TABLE #tmpTable
END
GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport1_1_4]    Script Date: 06/24/2011 13:42:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_1_4', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_1_4
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-07
-- Description:	This is for the weekly report 1.1.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport1_1_4]
	@startTime DATE
   ,@endTime DATE
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS DECIMAL(18,6)
	SET @exchangeRate=1.17*1000000*dbo.FuncGetExchangeRate()
	SELECT x.CATCode AS CATCode
		  ,w.CEWME AS CEWMESum
		  ,w.CEWMECount AS CEWMECount
		  ,u.TransferMoney AS TransferMoneySum
		  ,u.TransferCount AS TransferCount
		  ,v.CancelCount AS CancelCount FROM dbo.GetAssignedCAT(@employeeID) AS x
	LEFT OUTER JOIN (SELECT b.CATCode AS CATCode
		   ,COUNT(distinct a.PreContractID) AS CEWMECount
		   ,SUM(a.CEWME) AS CEWME
		FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS b ON b.Active=1 AND a.CATID=b.CATID
		WHERE a.AdjustID=21 AND (a.SignActual BETWEEN @startTime AND @endTime) AND a.ProvinceActive=1
		GROUP BY b.CATCode) AS w ON w.CATCode=x.CATCode
		
	LEFT OUTER JOIN (SELECT c.CATCode AS CATCode
	       ,COUNT(distinct a.PreContractID) AS TransferCount
	       ,SUM(a.TransferMoney)/@exchangeRate AS TransferMoney
	       FROM dbo.ContractTransfer AS a
		INNER JOIN dbo.vw_PreContractInfo AS b ON a.PreContractID=a.PreContractID
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS c ON c.Active=1 AND c.CATID=b.CATID
		WHERE a.TransferDate BETWEEN @startTime AND @endTime
		GROUP BY c.CATCode) AS u ON u.CATCode=x.CATCode
	
	LEFT OUTER JOIN (SELECT b.CATCode AS CATCode
	       ,COUNT(distinct a.PreContractID) AS CancelCount
	       FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedCAT(@employeeID) AS b ON b.Active=1 AND a.CATID=b.CATID
		WHERE a.SignActual BETWEEN @startTime AND @endTime
		GROUP BY b.CATCode) AS v ON v.CATCode=x.CATCode
	
	WHERE x.Active=1
END

GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport1_1_5]    Script Date: 06/24/2011 13:49:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_1_5', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_1_5
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-15
-- Description:	This is for weekly report 1.1.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport1_1_5] 
	@startTime DATE
   ,@endTime DATE
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS decimal(18,6)
	SET @exchangeRate=dbo.FuncGetExchangeRate()
	SET @exchangeRate=1.17*1000000*@exchangeRate
	CREATE TABLE #tmpTable(AdjustDisplayName NVARCHAR(15))
	INSERT INTO #tmpTable (AdjustDisplayName)
	VALUES ('Pre-'),('Test/Lease'),('Try&Buy')
	(SELECT
	  b.BGCode AS BGCode
	  ,c.BDCode AS BDCode
	  ,a.PreContractID AS PreContractID
	  ,a.OrionPrice AS OrionPrice
	  ,a.CEWME AS CEWME
	  ,a.OrionME AS OrionME
	  ,a.CFIPrice AS CFIPrice
	  ,a.SparePrice/@exchangeRate AS SparePrice
	  ,CASE a.AdjustID WHEN 21 THEN 'Pre-'
		WHEN 18 THEN 'Test/Lease'
		WHEN 20 THEN 'Test/Lease'
		WHEN 19 THEN 'Try&Buy'
		ELSE 'Others' END AS AdjustDisplayName
	FROM
	  vw_PreContractInfo AS a
	INNER JOIN dbo.GetAssignedBG(@employeeID) AS b ON b.Active=1 AND b.BGID=a.BGID
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS c ON c.Active=1 AND a.BDID=c.BDID
	WHERE a.PreContractAdjust IS NOT NULL AND (a.SignActual BETWEEN @startTime AND @endTime))
	UNION
	(SELECT a.BGCode AS BGCode
			,c.BDCode AS BDCode
			,NULL AS PreContractID
			,NULL AS OrieonPrice
			,NULL AS CEWME
			,NULL AS OrionME
			,NULL AS CFIPrice
			,NULL AS SparePrice
			,h.AdjustDisplayName AS AdjustDisplayName
	 FROM dbo.GetAssignedBG(@employeeID) AS a
	 INNER JOIN dbo.GetAssignedBD(@employeeID) AS c ON c.Active=1 AND c.BGID=a.BGID
	 CROSS JOIN #tmpTable AS h
	WHERE a.Active=1 --AND (NOT EXISTS (SELECT TOP 1 b.PreContractID FROM dbo.vw_PreContractInfo AS b WHERE (a.ProvinceClusterID = b.ClusterID) AND (b.PreContractAdjust IS NOT NULL)))
	)
	DROP TABLE #tmpTable
END

GO

/****** Object:  StoredProcedure [dbo].[WeeklyReport1_1_6]    Script Date: 06/24/2011 13:49:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.WeeklyReport1_1_6', 'P') IS NOT NULL
	DROP PROCEDURE dbo.WeeklyReport1_1_6
GO
-- =============================================
-- Author:		Tristan Luo
-- Create date: 2011-06-07
-- Description:	This is for the weekly report 1.1.
-- =============================================
CREATE PROCEDURE [dbo].[WeeklyReport1_1_6]
	@startTime DATE
   ,@endTime DATE
   ,@employeeID INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @exchangeRate AS DECIMAL(18,6)
	SET @exchangeRate=1.17*1000000*dbo.FuncGetExchangeRate()
	SELECT x.BGCode AS BGCode
		  ,y.BDCode AS BDCode
		  ,w.CEWME AS CEWMESum
		  ,w.CEWMECount AS CEWMECount
		  ,u.TransferMoney AS TransferMoneySum
		  ,u.TransferCount AS TransferCount
		  ,v.CancelCount AS CancelCount FROM dbo.GetAssignedBG(@employeeID) AS x
	INNER JOIN dbo.GetAssignedBD(@employeeID) AS y ON y.Active=1 AND x.BGID=y.BGID
		
	LEFT OUTER JOIN (SELECT b.BGCode AS BGCode
		   ,c.BDCode AS BDCode
		   ,COUNT(distinct a.PreContractID) AS CEWMECount
		   ,SUM(a.CEWME) AS CEWME
		FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedBG(@employeeID) AS b ON b.Active=1 AND a.BGID=b.BGID
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS c ON c.Active=1 AND c.BDID=a.BDID
		WHERE a.AdjustID=21 AND (a.SignActual BETWEEN @startTime AND @endTime) AND a.ProvinceActive=1
		GROUP BY b.BGCode, c.BDCode) AS w ON w.BGCode=x.BGCode AND w.BDCode=y.BDCode
		
	LEFT OUTER JOIN (SELECT c.BGCode AS BGCode
	       ,d.BDCode AS BDCode
	       ,COUNT(distinct a.PreContractID) AS TransferCount
	       ,SUM(a.TransferMoney)/@exchangeRate AS TransferMoney
	       FROM dbo.ContractTransfer AS a
		INNER JOIN dbo.vw_PreContractInfo AS b ON a.PreContractID=a.PreContractID
		INNER JOIN dbo.GetAssignedBG(@employeeID) AS c ON c.Active=1 AND c.BGID=b.BGID
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS d ON d.Active=1 AND d.BDID=b.BDID
		WHERE a.TransferDate BETWEEN @startTime AND @endTime
		GROUP BY c.BGCode, d.BDCode) AS u ON u.BGCode=x.BGCode AND u.BDCode=y.BDCode
	
	LEFT OUTER JOIN (SELECT b.BGCode AS BGCode
	       ,c.BDCode AS BDCode
	       ,COUNT(distinct a.PreContractID) AS CancelCount
	       FROM dbo.vw_PreContractInfo AS a
		INNER JOIN dbo.GetAssignedBG(@employeeID) AS b ON b.Active=1 AND a.BGID=b.BGID
		INNER JOIN dbo.GetAssignedBD(@employeeID) AS c ON c.Active=1 AND c.BDID=a.BDID
		WHERE a.SignActual BETWEEN @startTime AND @endTime
		GROUP BY b.BGCode, c.BDCode) AS v ON v.BGCode=x.BGCode AND v.BDCode=y.BDCode
	
	WHERE x.Active=1
END

GO


